import datetime
import hashlib
import os
import time
import urllib
import urllib.request
from enum import Enum
import uuid
import re

import pandas as pd

from server.pao_python.pao.data import dataframe_to_list, date_to_string
from server.pao_python.pao.service.data.mongo_db import (C, F, MongoFilter,
                                                         MongoService, N,
                                                         as_date)

from ...models.financial_manage import FinancialAccount
from ...pao_python.pao.data import get_cur_time, process_db, string_to_date
from ...service.buss_mis.financial_account import FinancialAccountObject
from ...service.buss_pub.bill_manage import (BillManageService, OperationType,
                                             Status, TypeId)
from ...service.buss_pub.personnel_organizational import UserCategory, UserType
from ...service.common import (SecurityConstant, UserType, find_data,
                               get_current_organization_id,
                               get_current_role_id, get_current_user_id,
                               get_info, get_str_to_age, get_string_time,
                               insert_data, update_data, insert_many_data)
from ...service.constant import (AccountStatus, AccountType, PayStatue,
                                 PayType, plat_id)
from ...service.mongo_bill_service import MongoBillFilter
from ...service.welfare_institution.accommodation_process import CheckInStatus

'''
@Description: In User Settings Edit
@Author: your name
@Date: 2019-09-25 09:48:29
@LastEditTime: 2019-10-31 15:50:52
@LastEditors: Please set LastEditors
'''
'''
版权：Copyright (c) 2019 China

创建日期：Tuesday September 17th 2019
创建者：ymq(ymq) - <<email>>

修改日期: Tuesday, 17th September 2019 9:52:29 am
修改者: ymq(ymq) - <<email>>

说明
 1、用户管理服务
'''

# 床位状态


class BedStatus(Enum):
    BlockUp = '停用'
    Empty = '空闲'
    Order = 'Order'
    Using = 'Using'
    TryCheckIn = 'TryCheckIn'

# 入住类型


class SignType(Enum):
    TryCheckIn = '试入住'
    FormalCheckIn = '正式入住'

# 入住状态


class CheckInStatus2(Enum):
    UnCheckIn = '待入住'
    CheckIn = '在住'
    Leave = '请假'
    CheckOut = '退住'

# 工单状态


class RecordStatus(Enum):
    WaitingDelivery = '待派工'
    WaitingExecuted = '待执行'
    Ongoing = '进行中'
    Completed = '已完成'

# 回访状态


class VisitStatus(Enum):
    NoVisited = '未回访'
    Visited = '已回访'

# 回访服务满意度


class ServiceEvaluation(Enum):
    VerySatisfied = '非常满意'
    Satisfied = '满意'
    General = '一般'
    Disappointment = '不满意'

# 服务周期


class Cycle(Enum):
    EveryDay = '每天'
    EveryWeek = '每周'
    EveryMonth = '每月'

# 支付方式


class PayStatus(Enum):
    NoPaid = '待支付'
    Paid = '已支付'

# 订单来源类型


class SourceType(Enum):
    Service = '服务'
    Goods = '商品'
    MonthBase = '月基础'
    BedPrice = '床位费'
    ChangeBed = '换床'
    Leave = '请假'
    Moment = '阶段性费用 '
    MonthService = '月服务'
    OnceProject = '一次性费用'
    CommodityCharge = '商品订单'
    CMDYTotalCharge = '商品总费用'
    CheckOut = '退住'
    InterimOrder = 'APP临时工单'
    Water = '冷水费'
    HotWater = '热水费'
    Electric = '电费'
    Expense = '长者证抵扣'
    Coupon = '积分抵扣'
    BedSubsidy = '床位费抵扣'
    Outpatient = '门诊'
    NursingCare = '托养医疗'
    HospitalExpenses = '住院费'
    ExaminationFee = '体检费'
    CableTV = '电视'
    Activity = '活动'
    Promotion = '试入住转正抵扣费'

# 付款方法


class BuyType(Enum):
    Cash = '现金'
    WipeCard = '刷卡'
    ExpenseCard = '消费卡'
    WeChat = '微信支付'
    Alipay = '支付宝'
    OnCredit = '挂账'
    Free = '减免'
    Deduction = '套餐抵扣'
    Allowance = '补贴账户'
    PackagePay = '套餐扣款'

# 订单创建方法


class CreateMethod(Enum):
    Manual = '手动'
    Automatic = '自动'

# 人员类别


class Sex(Enum):
    Female = '女'
    Male = '男'

# 活动状态


class ActivityStatus(Enum):
    UnStart = '未开始'
    OnGoing = '进行中'
    Finished = '已结束'


# 工作人员类别


class WorkerCategory(Enum):
    Management = '管理人员'
    Professional = '专业技能人员'
    SocialWorker = '社工人员'
    Logistics = '后勤人员'

# 机构类别


class JiGouLeiBie(Enum):
    WelfareCentre = '养老院'
    Happiness = '幸福院'
    Common = '运营机构'
    Provider = '服务商'
    Street = '街道'
    Government = '政府'
    Community = '社区'

# 养老类型


class YangLaoLeiXing(Enum):
    OrgType = '机构养老'
    HomeType = '居家养老'
# 养老类型


class ZhangZheLeiBie(Enum):
    Expense = '自费'
    ThreeNo = '三无'
    FiveProtect = '五保'
    Lonely = '孤寡'
    Subsistence = '享受低保救济'

# 学历资质


class XueLiZiZhi(Enum):
    SmallSchool = '小学'
    HighSchool = '中学'
    PolytechnicSchool = '中专'
    HigherVocationalEducation = '高职'
    JuniorCollege = '大专'
    RegularCollegeCourse = '本科'
    Master = '硕士'
    Doctor = '博士'
    No = '无'

# 岗位枚举


class Post(Enum):
    Dean = '院长'
    Doctor = '医生'
    Logistics = '后勤'
    Nurse = '护士'
    PipeNurse = '护管员'
    SocialWorker = '社工'


class ImportExcelService(MongoService):

    def __init__(self, db_addr, db_port, db_name, db_user, db_pwd, inital_password, session):
        self.db_addr = db_addr
        self.db_port = db_port
        self.db_name = db_name
        self.db_user = db_user
        self.db_pwd = db_pwd
        self.inital_password = inital_password
        self.session = session
        self.bill_manage_service = BillManageService(
            db_addr, db_port, db_name, db_user, db_pwd, inital_password, session)

    def date_to_format(self, num):
        if type(num) == float:
            num = int(num)
        if type(num) == int:
            num = num-2
            initDate = datetime.datetime.strptime(
                '1900-01-01 00:00:00', '%Y-%m-%d %H:%M:%S')
            return initDate+datetime.timedelta(days=+num)
            # return (initDate+datetime.timedelta(days=+num)).strftime("%Y-%m-%d %H:%M:%S")
        else:
            return num

    def strtodate(self, string):
        try:
            return datetime.datetime.strptime(
                string, '%Y-%m-%d %H:%M:%S')
        except Exception as e:
            return string

    def import_excel_api(self, data):
        # return self.import_excel_sys_person(data)
        if '订单编号' in data[0]:
            return self.import_excel_service_order(data)
        elif '床位主键' in data[0]:
            return self.import_excel_bed(data)
        elif '工单号码' in data[0]:
            return self.import_excel_service_record(data)
        elif '签到模式' in data[0]:
            return self.import_excel_signin(data)
        elif '活动介绍' in data[0]:
            return self.import_excel_activity(data)
        elif '资讯主键' in data[0]:
            return self.import_excel_article(data)
        elif '服务套餐主键' in data[10]:
            return self.import_excel_service_product(data)
        elif '义工名字' in data[10]:
            return self.import_excel_volunteer(data)

    def import_excel_test(self, data):

        _filter = MongoBillFilter()
        _filter = _filter.match(
            (C('personnel_info.personnel_category') == '长者'))
        res = self.query(
            _filter, 'PT_User')

        arr = []

        if len(res) > 0:
            for item in res:
                # 慈善账户
                subsidy_account_data = FinancialAccountObject(
                    AccountType.account_charitable, item['id'], None, AccountStatus.normal, 4, AccountType.account_charitable, {}, 0)
                subsidy_account = get_info(
                    subsidy_account_data.to_dict(), self.session)
                subsidy_account['GUID'] = str(uuid.uuid1())
                subsidy_account['version'] = 1
                subsidy_account['bill_status'] = 'valid'
                subsidy_account['valid_bill_id'] = str(uuid.uuid1())
                subsidy_account['bill_operator'] = '23b3630a-d92c-11e9-8b9a-983b8f0bcd67'

                arr.append(subsidy_account)

        def process_func(db):
            insert_many_data(db, 'ZZZPT_Financial_Account', arr)
        process_db(self.db_addr, self.db_port, self.db_name,
                   process_func, self.db_user, self.db_pwd)

    def update_personnel(self, data):
        # 修改人员
        pf = pd.DataFrame(data)
        pf.rename(columns={'status': 'zszt', '区域编号': 'qybh', '行政区划': 'xzqh', '人员姓名': 'ryxm', '性别': 'xb', '出生日期': 'csrq', '身份证号': 'sfzh', '所属组织': 'sszz', '特殊人群': 'tsrq', '残疾人等级': 'cjrdj', '社保类型': 'sblx', '户籍性质': 'hjxz', '人户分离': 'rhfl', '与户主关系': 'yhzgx', '户籍地址': 'hjdz',
                           '户口迁入地': 'hkqrd', '迁入时间': 'qrsj', '房屋类型': 'fwlx', '工作单位': 'gzdw', '固定电话': 'gddh', '移动电话': 'yddh', '民族': 'mz', '政治面貌': 'zzmm', '学历': 'xl', '毕业院校': 'byyx', '毕业时间': 'bysj', '职业类别': 'zylb', '婚姻状态': 'hyzt', '宗教信仰': 'zjxy', '兴趣爱好': 'xqah', '备注': 'bz', '长者类型': 'zzlx', '长者类别': 'zzlb', '养老类型': 'yllx', '创建日期': 'cjrq'}, inplace=True)
        condition = dataframe_to_list(pf)

        usertb = 'PT_User'
        financialtb = 'PT_Financial_Account'
        setroletb = 'PT_Set_Role'
        area_str = ''

        # 默认佛山市区域ID
        default_area_id = 'da1e4201-70c5-407a-a088-c3a8cc91fef5'
        # 默认游客的角色ID
        default_role_id = '8ed260f6-e355-11e9-875e-a0a4c57e9ebe'
        # 默认平台的组织ID
        default_organization_id = '7e7e2fec-d91d-11e9-8e1d-983b8f0bcd67'

        temp_qybh = {}
        temp_sszz = {}
        temp_zzxl = {}

        count = 0

        ryxm = self.clearZFC(condition[0]['ryxm'])

        if ryxm == '硬件演示':
            area_str = 'cc'
            area_cn = '禅城'
        elif ryxm == '岑美梅':
            area_str = 'dl'
            area_cn = '大沥'
        elif ryxm == '李惠兴':
            area_str = 'dz'
            area_cn = '丹灶'
        elif ryxm == '黎丹丹':
            area_str = 'gc'
            area_cn = '桂城'
        elif ryxm == '李金弟':
            area_str = 'jj'
            area_cn = '九江'
        elif ryxm == '吴玉兰':
            area_str = 'na'
            area_cn = '空编号'
        elif ryxm == '柯少君':
            area_str = 'ls'
            area_cn = '里水'
        elif ryxm == '揭绍明':
            area_str = 'ot'
            area_cn = '其他'
        elif ryxm == '刘惠英':
            area_str = 'xq'
            area_cn = '西樵'
        elif ryxm == '刘巨亨':
            area_str = 'ss'
            area_cn = '狮山'
        elif ryxm == '吴秋庆':
            area_str = 'ss'
            area_cn = '狮山'

        usertb = usertb + '_' + area_str
        financialtb = financialtb + '_' + area_str
        setroletb = setroletb + '_' + area_str
        # 找出有没有
        if len(condition) > 0:
            for item in condition:

                ryxm = self.clearZFC(item['ryxm'])
                sfzh = self.clearZFC(item['sfzh'])

                print(sfzh != None)

                if sfzh != None:
                    # 这里根据身份证做判断
                    _filter_isHave = MongoBillFilter()
                    _filter_isHave.match_bill(C('personnel_info.id_card') == sfzh)\
                        .project({'_id': 0})
                    res_isHave = self.query(
                        _filter_isHave, usertb)
                else:
                    # 这里根据名字做判断
                    _filter_isHave = MongoBillFilter()
                    _filter_isHave.match_bill((C('name') == ryxm) & ((C('personnel_info.id_card') == 'None') | (C('personnel_info.id_card') == N()) | (C('personnel_info.id_card') == "")))\
                        .project({'_id': 0})
                    res_isHave = self.query(
                        _filter_isHave, usertb)

                if len(res_isHave) > 0:
                    # if 'import_date' in res_isHave[0]:
                    #     continue
                    # 更新的
                    save_data = {
                        **res_isHave[0]
                    }
                else:
                    # 新增的
                    save_data = {
                        "name": "",
                        "personnel_type": "1",
                        "admin_area_id": default_area_id,
                        "town": "",
                        "id_card_type": "身份证",
                        "id_card": "",
                        "organization_id": default_organization_id,
                        "reg_date": '',
                        "login_info": [],
                        "personnel_info": {
                            "name": "",
                            "id_card": "",
                            "date_birth": "",
                            "sex": "",
                            "telephone": "",
                            "remarks": "",
                            "personnel_category": "长者",
                            "address": "",
                            "role_id": default_role_id,
                            "is_alive": "",
                            "personnel_classification": "",
                            "pension_category": "",
                            "elders_category": "",
                            "education": "",
                            "political_outlook": "",
                        }
                    }

                # 修改的名字
                save_data['name'] = ryxm
                save_data['personnel_info']['name'] = ryxm

                # 判断所属行政编号
                if 'qybh' in item and item['qybh'] != None:
                    qybh = self.clearZFC(item['qybh'])
                    if qybh in temp_qybh:
                        print('已有区域ID，无需重复获取')
                        save_data['admin_area_id'] = temp_qybh[qybh]
                    else:
                        _filter_qybh = MongoBillFilter()
                        _filter_qybh.match_bill((C('code') == qybh))\
                            .project({'_id': 0})
                        res_qybh = self.query(
                            _filter_qybh, 'PT_Administration_Division56')

                        if len(res_qybh) > 0:
                            temp_qybh[qybh] = res_qybh[0]['id']
                            save_data['admin_area_id'] = res_qybh[0]['id']

                # 判断所属组织
                if 'sszz' in item and item['sszz'] != None:
                    sszz = self.clearZFC(item['sszz'])
                    if sszz in temp_sszz:
                        print('已有组织ID，无需重复获取')
                        save_data['organization_id'] = temp_sszz[sszz]
                    else:
                        _filter_sszz = MongoBillFilter()
                        _filter_sszz.match_bill((C('name') == sszz))\
                            .project({'_id': 0})
                        res_sszz = self.query(
                            _filter_sszz, 'PT_User56')

                        if len(res_sszz) > 0:
                            temp_sszz[sszz] = res_sszz[0]['id']
                            save_data['organization_id'] = res_sszz[0]['id']

                # 判断长者类型
                if 'zzlx' in item and item['zzlx'] != None:
                    zzlx = self.clearZFC(item['zzlx'])
                    if zzlx in temp_zzxl:
                        print('已有长者类型，无需重复获取')
                        save_data['personnel_info']['personnel_classification'] = temp_zzxl[zzlx]
                    else:
                        _filter_zzlx = MongoBillFilter()
                        _filter_zzlx.match_bill((C('name') == zzlx))\
                            .project({'_id': 0})
                        res_zzlx = self.query(
                            _filter_zzlx, 'PT_Personnel_Classification')

                        if len(res_zzlx) > 0:
                            temp_zzxl[zzlx] = res_zzlx[0]['id']
                            save_data['personnel_info']['personnel_classification'] = res_zzlx[0]['id']

                # 判断生日
                if 'crrq' in item and item['crrq'] != None:
                    crrq = self.strtodate(self.clearZFC(item['crrq']))
                    save_data['personnel_info']['date_birth'] = crrq
                # 判断创建日期
                if 'cjrq' in item and item['cjrq'] != None:
                    cjrq = self.strtodate(self.clearZFC(item['cjrq']))
                    save_data['reg_date'] = cjrq
                    save_data['create_date'] = cjrq
                # 政治面貌
                save_data['personnel_info']['political_outlook'] = self.clearZFC(
                    item['zzmm'])
                # 民族
                save_data['personnel_info']['nation'] = self.clearZFC(
                    item['mz'])
                # 婚姻状态
                save_data['personnel_info']['remarks'] = self.clearZFC(
                    item['bz'])

                # 养老类型
                if 'xl' in item and item['xl'] != None:
                    xl = self.clearZFC(item['xl'])
                    if xl in XueLiZiZhi.__members__:
                        save_data['personnel_info']['education'] = XueLiZiZhi[xl].value
                    else:
                        save_data['personnel_info']['education'] = ''
                else:
                    save_data['personnel_info']['education'] = ''

                # 身份证号
                save_data['id_card'] = sfzh
                save_data['personnel_info']['id_card'] = sfzh

                # 性别
                if 'xb' in item:
                    xb = self.clearZFC(item['xb'])
                    save_data['personnel_info']['sex'] = '男' if xb == 'Male' else '女'
                # 地址
                if 'hjdz' in item and item['hjdz'] != None:
                    hjdz = self.clearZFC(item['hjdz'])
                    save_data['personnel_info']['address'] = hjdz
                # 电话
                if 'yddh' in item and item['yddh'] != None:
                    yddh = self.clearZFC(item['yddh'])
                    save_data['personnel_info']['telephone'] = yddh

                # 没有移动电话就用固定电话试试
                if 'telephone' not in save_data['personnel_info'] or ('telephone' in save_data['personnel_info'] and save_data['personnel_info']['telephone'] == '') and 'gddh' in item and item['gddh'] != None:
                    gddh = self.clearZFC(item['gddh'])
                    save_data['personnel_info']['telephone'] = gddh

                # 在世状态
                if 'zszt' in item and item['zszt'] != None:
                    zszt = self.clearZFC(item['zszt'])
                    save_data['personnel_info']['is_alive'] = True if zszt == 'Normal' else False

                # 养老类型
                if 'yllx' in item and item['yllx'] != None:
                    yllx = self.clearZFC(item['yllx'])
                    if yllx in YangLaoLeiXing.__members__:
                        save_data['personnel_info']['pension_category'] = YangLaoLeiXing[yllx].value
                    else:
                        save_data['personnel_info']['pension_category'] = ''
                else:
                    save_data['personnel_info']['pension_category'] = ''
                # 长者类别
                if 'zzlb' in item and item['zzlb'] != None:
                    zzlb = self.clearZFC(item['zzlb'])
                    if zzlb in ZhangZheLeiBie.__members__:
                        save_data['personnel_info']['elders_category'] = ZhangZheLeiBie[zzlb].value
                    else:
                        save_data['personnel_info']['elders_category'] = ''
                else:
                    save_data['personnel_info']['elders_category'] = ''

                # 导入标识
                save_data['import_date'] = '20191226'

                if len(res_isHave) > 0:
                    # 更新的
                    def process_func(db):
                        # print(save_data)
                        # return save_data
                        update_data(db, usertb, save_data, {
                            'id': res_isHave[0]['id'],
                        })
                    process_db("127.0.0.1", 27017, 'IEC',
                               process_func, 'admin', '123456')
                else:
                    data = get_info(save_data, self.session)
                    # return data

                    # 新增账户信息（1个机构储蓄、1个app储蓄、1个真实账户、1个补贴账户）
                    # 机构储蓄对象
                    org_account_data = FinancialAccountObject(
                        AccountType.account_recharg_wel, data['id'], data['organization_id'], AccountStatus.normal, 1, AccountType.account_recharg_wel, {}, 0)
                    org_account = get_info(
                        org_account_data.to_dict(), self.session)
                    # app储蓄对象
                    app_account_data = FinancialAccountObject(
                        AccountType.account_recharg_app, data['id'], plat_id, AccountStatus.normal, 2, AccountType.account_recharg_app, {}, 0)
                    app_account = get_info(
                        app_account_data.to_dict(), self.session)
                    # 真实账户对象
                    real_account_data = FinancialAccountObject(
                        AccountType.account_real, data['id'], None, AccountStatus.normal, 3, AccountType.account_real, {}, 0)
                    real_account = get_info(
                        real_account_data.to_dict(), self.session)
                    # 补贴账户对象
                    subsidy_account_data = FinancialAccountObject(
                        AccountType.account_subsidy, data['id'], None, AccountStatus.normal, 4, AccountType.account_subsidy, {}, 0)
                    subsidy_account = get_info(
                        subsidy_account_data.to_dict(), self.session)
                    tables = [usertb, financialtb]
                    datas = [[data], [org_account, app_account,
                                      real_account, subsidy_account]]

                    set_role = {'role_id': default_role_id,
                                'principal_account_id': data['id'], 'role_of_account_id': data['organization_id']}
                    set_role_data = get_info(set_role, self.session)
                    datas.append(set_role_data)
                    tables.append(setroletb)
                    print('开始导入人员：' + str(area_str) + '_' + str(data['name']))
                    try:
                        self.bill_manage_service.add_bill(OperationType.add.value,
                                                          TypeId.user.value, datas, tables)
                    except Exception as e:
                        print(e)

        return '导入成功'

    # 修复工作人员的role_id全是服务商工作人员的问题
    def update_worker(self, data):
        dbIp = "127.0.0.1"
        dbName = 'IEC'
        dbUser = 'admin'
        dbPwd = '123456'
        _filter_main = MongoBillFilter()
        _filter_main.match_bill((C('personnel_type') == '1') & (C('personnel_info.personnel_category') == '工作人员') & (C('import_date').inner(['20200108'])))\
            .project({'_id': 0})
        res_main = self.query(
            _filter_main, 'PT_User')

        count = 1

        if len(res_main) > 0:
            for item in res_main:
                print('第'+str(count)+'个    ' +item['id'])
                count = count+1
                # 根据organization_id去找所属组织
                _filter_organization = MongoBillFilter()
                _filter_organization.match((C('personnel_type') == '2') & (C('id') == item['organization_id']))\
                    .project({'_id': 0})
                res_organization = self.query(
                    _filter_organization, 'PT_User')

                # 先查set_role表对不对
                _filter_set_role = MongoBillFilter()
                _filter_set_role.match((C('principal_account_id') == item['id']) & (C('role_of_account_id') == item['organization_id']))\
                    .project({'_id': 0})
                res_set_role = self.query(
                    _filter_set_role, 'PT_Set_Role')

                if len(res_organization) > 0:
                    if res_organization[0]['organization_info']['personnel_category'] == '幸福院':
                        case_role_id = '8f3524cc-e355-11e9-9f87-a0a4c57e9ebe'
                        if len(res_set_role) > 0:
                            if res_set_role[0]['role_id'] == case_role_id:
                                continue
                        def process_func(db):
                            update_data(db, 'PT_Set_Role', {'role_id': case_role_id}, {
                                'principal_account_id': item['id'],
                                'role_of_account_id': item['organization_id'],
                                'bill_status': 'valid',
                            })
                        process_db(dbIp, 27017, dbName,
                                   process_func, dbUser, dbPwd)
                    elif res_organization[0]['organization_info']['personnel_category'] == '福利院':
                        case_role_id = '8e271ff0-e355-11e9-91a6-a0a4c57e9ebe'
                        if len(res_set_role) > 0:
                            if res_set_role[0]['role_id'] == case_role_id:
                                continue
                        def process_func(db):
                            update_data(db, 'PT_Set_Role', {'role_id': case_role_id}, {
                                'principal_account_id': item['id'],
                                'role_of_account_id': item['organization_id'],
                                'bill_status': 'valid',
                            })
                        process_db(dbIp, 27017, dbName,
                                   process_func, dbUser, dbPwd)
                    elif res_organization[0]['organization_info']['personnel_category'] == '服务商':
                        case_role_id = '8fa7a328-e355-11e9-98b5-a0a4c57e9ebe'
                        # 判断role_name是否包含管理员
                        if 'personnel_info' in item and 'role_name' in item['personnel_info']:
                            if '管理员' in item['personnel_info']['role_name']:
                                # 说明有多个角色
                                if ',' in item['personnel_info']['role_name']:
                                    # 增加一个服务商服务人员
                                    _filter_is_set = MongoBillFilter()
                                    _filter_is_set.match((C('role_id') == case_role_id) &(C('principal_account_id') == item['id']) & (C('role_of_account_id') == item['organization_id']))\
                                        .project({'_id': 0})
                                    res_is_set = self.query(
                                        _filter_is_set, 'PT_Set_Role')
                                    if len(res_is_set) == 0:
                                        self.bill_manage_service.add_bill(OperationType.add.value, TypeId.user.value, {
                                                                        'role_id': case_role_id, 'principal_account_id': item['id'], 'role_of_account_id': item['organization_id']}, 'PT_Set_Role')
                    else:
                        # 默认幸福院工作人员
                        case_role_id = '8f3524cc-e355-11e9-9f87-a0a4c57e9ebe'
                        if len(res_set_role) > 0:
                            if res_set_role[0]['role_id'] == case_role_id:
                                continue
                        def process_func(db):
                            update_data(db, 'PT_Set_Role', {'role_id': case_role_id}, {
                                'principal_account_id': item['id'],
                                'role_of_account_id': item['organization_id'],
                                'bill_status': 'valid',
                            })
                        process_db(dbIp, 27017, dbName,
                                   process_func, dbUser, dbPwd)
        print(len(res_main))

    def import_excel_worker(self, data):
        # 导入工作人员
        pf = pd.DataFrame(data)
        pf.rename(columns={'姓名': 'name', '性别': 'sex', '出生日期': 'date_birth', '身份证号码': 'id_card', '所属机构': 'organization_name',
                           '联系电话': 'telephone', '籍贯': 'native_place', '家庭地址': 'home_address', '身份证地址': 'id_card_address', '行政区划编号': "admin_area_id", '行政区域': 'admin_area', '照片': 'photo', '学历资质': 'education', "岗位": 'post', '角色': 'role_name', '护理区域': 'nursing_area', '人员类别': 'worker_category', '创建日期': 'create_date'}, inplace=True)
        condition = dataframe_to_list(pf)

        # 组织机构的缓存数组
        temp_org = {}
        # 区域编号的缓存数组
        temp_area = {}

        usertb = 'PT_User'
        financialtb = 'ZPT_Financial_Account'
        setroletb = 'ZPT_Set_Role'

        # 默认佛山市区域ID
        default_area_id = 'da1e4201-70c5-407a-a088-c3a8cc91fef5'
        # 默认服务商工作人员的角色ID
        default_role_id = '8f9809cc-e355-11e9-9d97-a0a4c57e9ebe'
        # 默认平台的组织ID
        default_organization_id = '7e7e2fec-d91d-11e9-8e1d-983b8f0bcd67'

        if len(condition) > 0:
            for item in condition:

                # 这里根据身份证做判断，没有身份证就用名字和机构判断是否有导入过，并且修改
                if 'id_card' in item and item['id_card'] != None:
                    id_card = self.clearZFC(item['id_card'])
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('personnel_info.id_card') == id_card) & (C('personnel_type') == '1') & (C('personnel_info.personnel_category') == '工作人员'))\
                        .project({'_id': 0})
                    res = self.query(
                        _filter, usertb)
                else:
                    if 'name' in item and item['name'] != None:
                        # 所属机构
                        if 'organization_name' in item and item['organization_name'] != None:
                            organization_name = self.clearZFC(
                                item['organization_name'])
                            organization_id = self._getOrganizationId(
                                organization_name, temp_org, default_organization_id)
                            temp_org[organization_name] = organization_id

                        # 名字
                        name = self.clearZFC(item['name'])
                        _filter = MongoBillFilter()
                        _filter.match_bill((C('personnel_info.name') == name) & (C('organization_id') == organization_id) & (C('personnel_type') == '1') & (C('personnel_info.personnel_category') == '工作人员'))\
                            .project({'_id': 0})
                        res = self.query(
                            _filter, usertb)
                    else:
                        res = []

                # create_date date_birth admin_area_id organization_id
                out_per = ['name', 'id_card']
                in_per = ['sex', 'id_card', 'telephone', 'native_place', 'home_address',
                          'id_card_address', 'education', 'post', 'nursing_area', 'star_level', 'worker_category', 'role_name']

                if len(res) > 0:
                    # 有旧数据
                    # 获取excel表对应的组织机构ID
                    if 'organization_name' in item and item['organization_name'] != None:
                        organization_name = self.clearZFC(
                            item['organization_name'])
                        res[0]['organization_id'] = self._getOrganizationId(
                            organization_name, temp_org, default_organization_id)
                        temp_org[organization_name] = res[0]['organization_id']

                    # 判断personnel_info里的数据是否一致
                    res[0] = self._setValues(out_per, item, res[0])

                    # 判断personnel_info里的数据是否一致
                    res[0]['personnel_info'] = self._setValues(
                        in_per, item, res[0]['personnel_info'])

                    # 创建时间
                    if 'create_date' in item:
                        defaultValue = self._getDefaultValue(
                            'create_date', res[0])
                        res[0]['create_date'] = self._setTimeFormat(
                            item['create_date'], defaultValue)
                        res[0]['reg_date'] = res[0]['create_date']
                    # 生日日期
                    if 'date_birth' in item:
                        defaultValue = self._getDefaultValue(
                            'date_birth', res[0]['personnel_info'])
                        res[0]['personnel_info']['date_birth'] = self._setTimeFormat(
                            item['date_birth'], defaultValue)
                    # 判断所属行政编号
                    if 'admin_area_id' in item:
                        admin_area_id = self.clearZFC(item['admin_area_id'])
                        defaultValue = self._getDefaultValue(
                            'admin_area_id', res[0], default_area_id)
                        res[0]['admin_area_id'] = self._getAdministratorArea(
                            admin_area_id, temp_area, defaultValue)
                        temp_area[admin_area_id] = res[0]['admin_area_id']
                    # 照片
                    if 'photo' in item and item['photo'] != None:
                        photo = self.get_url_upload_url(
                            self.clearZFC(item['photo']), 'workerPhoto')
                        if photo != '':
                            res[0]['personnel_info']['photo'] = [photo]

                    # 导入标识
                    # if 'import_date' in res[0]:
                    #     olds = res[0]['import_date']
                    #     res[0]['import_date'] = []
                    #     res[0]['import_date'].append(olds)
                    #     res[0]['import_date'].append('20200108')
                    # else:
                    #     res[0]['import_date'] = ['20200108']

                    def process_func(db):
                        update_data(db, usertb, res[0], {
                            'id': res[0]['id'],
                            'bill_status': 'valid',
                        })
                    process_db("127.0.0.1", 27017, 'IEC',
                               process_func, 'admin', '123456')
                else:
                    newData = {
                        "name": "",
                        "personnel_type": "1",
                        "admin_area_id": default_area_id,
                        "town": "",
                        "id_card_type": "身份证",
                        "id_card": "",
                        "organization_id": default_organization_id,
                        "reg_date": '',
                        "login_info": [],
                        "personnel_info": {
                            "name": "",
                            "id_card": "",
                            "date_birth": "",
                            "sex": "",
                            "telephone": "",
                            "remarks": "",
                            "personnel_category": "工作人员",
                            "address": "",
                            "role_id": default_role_id,
                            "is_alive": "",
                            "personnel_classification": "",
                            # 籍贯
                            "native_place": '',
                            # 身份证地址
                            "id_card_address": '',
                            # 护理区域
                            "nursing_area": '',
                            # 岗位
                            "post": '',
                            # 学历资质
                            "education": '',
                            # 角色
                            "role_name": '',
                            # 工作人员类别
                            "worker_category": '',
                            # 照片
                            "photo": [],
                        }
                    }
                    # 判断personnel_info里的数据是否一致
                    newData = self._setValues(out_per, item, newData)
                    # 判断personnel_info里的数据是否一致
                    newData['personnel_info'] = self._setValues(
                        in_per, item, newData['personnel_info'])
                    # 获取excel表对应的组织机构ID
                    if 'organization_name' in item and item['organization_name'] != None:
                        organization_name = self.clearZFC(
                            item['organization_name'])
                        newData['organization_id'] = self._getOrganizationId(
                            organization_name, temp_org, default_organization_id)
                        temp_org[organization_name] = newData['organization_id']
                    # 创建时间
                    if 'create_date' in item:
                        newData['create_date'] = self._setTimeFormat(
                            item['create_date'])
                        newData['reg_date'] = newData['create_date']
                    # 生日日期
                    if 'date_birth' in item:
                        newData['personnel_info']['date_birth'] = self._setTimeFormat(
                            item['date_birth'])
                    # 判断所属行政编号
                    if 'admin_area_id' in item:
                        admin_area_id = self.clearZFC(item['admin_area_id'])
                        newData['admin_area_id'] = self._getAdministratorArea(
                            admin_area_id, temp_area, default_area_id)
                        temp_area[admin_area_id] = newData['admin_area_id']
                    # 照片
                    if 'photo' in item and item['photo'] != None:
                        photo = self.get_url_upload_url(
                            self.clearZFC(item['photo']), 'workerPhoto')
                        if photo != '':
                            newData['personnel_info']['photo'] = [photo]

                    # 导入标识
                    # newData['import_date'] = ['20200108']

                    data = get_info(newData, self.session)

                    # 建立四个账户表
                    financialAccountsResult = self._getFinancialAccount(data)

                    data = financialAccountsResult[0]

                    tables = [usertb, financialtb]
                    datas = [[data], financialAccountsResult[1]]

                    set_role = {'role_id': default_role_id,
                                'principal_account_id': data['id'], 'role_of_account_id': data['organization_id']}
                    set_role_data = get_info(set_role, self.session)
                    datas.append(set_role_data)
                    tables.append(setroletb)
                    try:
                        self.bill_manage_service.add_bill(OperationType.add.value,
                                                          TypeId.user.value, datas, tables)
                    except Exception as e:
                        print(e)
        return '导入成功'

    # 给user表的数据设置账户表
    def _getFinancialAccount(self, data):
        if 'organization_info' in data:
            # 组织类型为福利院
            if data['organization_info']['personnel_category'] == '福利院':
                # 新增一个真实账户
                # 真实账户对象
                real_account_data = FinancialAccountObject(
                    AccountType.account_real, data['id'], None, AccountStatus.normal, 1, AccountType.account_real, {}, 0)
                real_account = get_info(
                    real_account_data.to_dict(), self.session)
                data['agent_account'] = real_account['id']
                data['main_account'] = real_account['id']
                return [data, [real_account]]
            # 组织类型为服务商
            if data['organization_info']['personnel_category'] == '服务商':
                # 新增一个真实账户 + 补贴账户
                # 真实账户对象
                real_account_data = FinancialAccountObject(
                    AccountType.account_real, data['id'], None, AccountStatus.normal, 1, AccountType.account_real, {}, 0)
                real_account = get_info(
                    real_account_data.to_dict(), self.session)
                data['agent_account'] = real_account['id']
                data['main_account'] = real_account['id']
                # 补贴账户
                subsidy_account_data = FinancialAccountObject(
                    AccountType.account_subsidy, data['id'], None, AccountStatus.normal, 2, AccountType.account_subsidy, {}, 0)
                subsidy_account = get_info(
                    subsidy_account_data.to_dict(), self.session)
                return [data, [real_account, subsidy_account]]
        elif 'personnel_info' in data:
            # 个人用户创建四个账户

            # 新增账户信息（1个机构储蓄、1个app储蓄、1个真实账户、1个补贴账户）
            # 机构储蓄对象
            org_account_data = FinancialAccountObject(
                AccountType.account_recharg_wel, data['id'], data['organization_id'], AccountStatus.normal, 1, AccountType.account_recharg_wel, {}, 0)
            org_account = get_info(
                org_account_data.to_dict(), self.session)
            # app储蓄对象
            app_account_data = FinancialAccountObject(
                AccountType.account_recharg_app, data['id'], plat_id, AccountStatus.normal, 2, AccountType.account_recharg_app, {}, 0)
            app_account = get_info(
                app_account_data.to_dict(), self.session)
            # 真实账户对象
            real_account_data = FinancialAccountObject(
                AccountType.account_real, data['id'], None, AccountStatus.normal, 3, AccountType.account_real, {}, 0)
            real_account = get_info(
                real_account_data.to_dict(), self.session)
            # 补贴账户对象
            subsidy_account_data = FinancialAccountObject(
                AccountType.account_subsidy, data['id'], None, AccountStatus.normal, 4, AccountType.account_subsidy, {}, 0)
            subsidy_account = get_info(
                subsidy_account_data.to_dict(), self.session)
            # 慈善账户
            charitable_account_data = FinancialAccountObject(
                AccountType.account_charitable, data['id'], None, AccountStatus.normal, 5, AccountType.account_charitable, {}, 0)
            charitable_account = get_info(
                charitable_account_data.to_dict(), self.session)
            return [data, [org_account, app_account,
                           real_account, subsidy_account, charitable_account]]
        return False

    def import_excel_service_item(self, data):
        # 导入根据服务产品，导入服务项目，再生成服务产品
        pf = pd.DataFrame(data)
        pf.rename(columns={'服务方式': 'fwfs', '服务项目内容': 'fwxmnr', '服务项目价格': 'fwxmjg', '服务项目销售量': 'fwxmxsl', '服务周期': 'fwzq', '是否发布到app': 'sffbdapp',
                           '服务类型': 'fwlx', '审核状态': 'shzt', '审核建议': 'shjy', '审核日期': 'shrq', '封面图片': 'fmtp', '服务项目图片': 'fwxmtp', '服务机构': 'fwjg', '服务项目名称': 'fwxmmc', '创建日期': 'cjrq'}, inplace=True)
        condition = dataframe_to_list(pf)

        # 服务选项数据表
        service_option_table = 'ZPT_Service_Option'
        # 服务项目数据表
        service_item_table = 'ZPT_Service_Item'
        # 服务产品数据表
        service_product_table = 'ZPT_Service_Product'

        # 服务选项
        # 判断有没有import_date为20191231的服务选项，以这个为单价
        _filter_fwxx = MongoBillFilter()
        _filter_fwxx.match_bill((C('import_date') == '20191231'))\
            .project({'_id': 0})
        res_fwxx = self.query(
            _filter_fwxx, service_option_table)

        if len(res_fwxx) == 0:
            # 没有，新创一个
            new_service_option = {
                "name": "服务商产品单价",
                "default_value": "0",
                "is_modification_mermissible": "是",
                "remarks": "服务商产品单价",
                "option_type": "service_before",
                "option_value_type": "3",
                # 这是平台的组织机构ID
                "organization_id": "7e7e2fec-d91d-11e9-8e1d-983b8f0bcd67",
                "option_value": "0",
                "import_date": "20191231"
            }
            new_service_option_info = get_info(
                new_service_option, self.session)

            service_option = new_service_option_info

            self.bill_manage_service.add_bill(OperationType.add.value,
                                              TypeId.serviceItem.value, new_service_option_info, service_option_table)
        else:
            service_option = res_fwxx[0]

        # 组织机构的储存
        temp_fwjg = {}

        if len(condition) > 0:
            for item in condition:
                # 服务项目基础模板
                default_service_item = {
                    "name": "",
                    "periodicity": "",
                    "organization_id": "",
                    "item_type": [],
                    "service_project_website": "",
                    "application_scope": [],
                    "is_standard_service": "是",
                    "is_allowance": "1",
                    "proportion": [
                        {
                            "contents": 0,
                            "title": "平台"
                        },
                        {
                            "contents": 1,
                            "title": "服务商"
                        },
                        {
                            "contents": 0,
                            "title": "慈善"
                        }
                    ],
                    "valuation_formula": [
                        {
                            "name": "标准计价公式",
                            "remark": "标准计价公式",
                            "formula": "float('服务商产品单价')"
                        }
                    ],
                    "contract_clauses": "",
                    "service_option": [
                        {
                            "default_value": service_option['default_value'],
                            "id":service_option['id'],
                            "is_modification_mermissible":service_option['is_modification_mermissible'],
                            "name":service_option['name'],
                            "option_type":service_option['option_type'],
                            "option_value_type":service_option['option_value_type'],
                            "remarks":service_option['remarks'],
                        }
                    ],
                    "import_date": "20191231"
                }

                # 替换导入的数据
                fwxmmc = self.clearZFC(item['fwxmmc'])
                default_service_item['name'] = fwxmmc
                # 服务类型
                if 'fwlx' in item and item['fwlx'] != None:
                    default_service_item['item_type'] = [
                        self.clearZFC(item['fwlx'])]
                # else:
                    # default_service_product['item_type'] = []

                # 服务周期
                if 'fwzq' in item and item['fwzq'] != None:
                    default_service_item['periodicity'] = self.clearZFC(
                        item['fwzq'])
                else:
                    default_service_product['periodicity'] = ''

                # 判断所属机构
                if 'fwjg' in item and item['fwjg'] != None:
                    fwjg = self.clearZFC(item['fwjg'])
                    if fwjg in temp_fwjg:
                        default_service_item['organization_id'] = temp_fwjg[fwjg]
                        # 赋值。下面生成服务产品就不用重复找了
                        item['organization_id'] = temp_fwjg[fwjg]
                    else:
                        _filter_fwjg = MongoBillFilter()
                        _filter_fwjg.match_bill((C('name') == fwjg))\
                            .project({'_id': 0})
                        res_fwjg = self.query(
                            _filter_fwjg, 'PT_User56test')

                        if len(res_fwjg) > 0:
                            temp_fwjg[fwjg] = res_fwjg[0]['id']
                            default_service_item['organization_id'] = res_fwjg[0]['id']
                            # 赋值。下面生成服务产品就不用重复找了
                            item['organization_id'] = res_fwjg[0]['id']

                # 导入一个服务选项
                service_option_info = get_info(
                    default_service_item, self.session)

                self.bill_manage_service.add_bill(OperationType.add.value,
                                                  TypeId.serviceItem.value, service_option_info, service_item_table)
                # 服务产品基础模板
                default_service_product = {
                    "name": "服务产品名称",
                    "introduce": "服务产品描述",
                    "remarks": "",
                    "state": 1,
                    "is_assignable": "1",
                    "is_external": "1",
                    "is_recommend": "是",
                    "organization_id": item['organization_id'],
                    "service_product_type": [],
                    "remaining_times": "1",
                    "picture_collection": [],
                    "additonal_contract_terms": "",
                    "service_product_introduce": "",
                    "is_service_item": "true",
                    "service_item_id": service_option_info['id'],
                    "valuation_formula": service_option_info['valuation_formula'],
                    "proportion": [
                        {
                            "contents": 0,
                            "title": "平台"
                        },
                        {
                            "contents": 1,
                            "title": "服务商"
                        },
                        {
                            "contents": 0,
                            "title": "慈善"
                        }
                    ],
                    "service_option": [
                        {
                            "default_value": service_option['default_value'],
                            "id":service_option['id'],
                            "is_modification_mermissible":service_option['is_modification_mermissible'],
                            "name":service_option['name'],
                            "option_type":service_option['option_type'],
                            "option_value_type":service_option['option_value_type'],
                            "remarks":service_option['remarks'],
                            # 这里要用价格
                            "option_value":self.clearZFC(item['fwxmjg'])
                        }
                    ],
                    "import_date": "20191231"
                }
                # 已有字段
                # 服务项目名称
                if 'fwxmmc' in item and item['fwxmmc'] != None:
                    default_service_product['name'] = self.clearZFC(
                        item['fwxmmc'])
                else:
                    default_service_product['name'] = ''
                # 服务项目内容
                if 'fwxmnr' in item and item['fwxmnr'] != None:
                    default_service_product['introduce'] = self.clearZFC(
                        item['fwxmnr'])
                else:
                    default_service_product['introduce'] = ''
                # 服务项目图片
                if 'fwxmtp' in item and item['fwxmtp'] != None:
                    fwxmtp = self.get_url_upload_url(
                        self.clearZFC(item['fwxmtp']))
                    if fwxmtp != '':
                        default_service_product['picture_collection'] = [
                            fwxmtp]
                else:
                    default_service_product['picture_collection'] = []
                # 以下字段是原表没有，但是额外储存的
                # 服务方式
                if 'fwfs' in item and item['fwfs'] != None:
                    default_service_product['service_mode'] = self.clearZFC(
                        item['fwfs'])
                else:
                    default_service_product['service_mode'] = ''
                # 销售量
                if 'fwxmxsl' in item and item['fwxmxsl'] != None:
                    default_service_product['sales_volume'] = self.clearZFC(
                        item['fwxmxsl'])
                else:
                    default_service_product['sales_volume'] = ''
                # 是否发布到APP
                if 'sffbdapp' in item and item['sffbdapp'] != None:
                    default_service_product['is_app'] = self.clearZFC(
                        item['sffbdapp'])
                else:
                    default_service_product['is_app'] = ''
                # 审核状态
                if 'shzt' in item and item['shzt'] != None:
                    default_service_product['audit_status'] = self.clearZFC(
                        item['shzt'])
                else:
                    default_service_product['audit_status'] = ''
                # 审核建议
                if 'shjy' in item and item['shjy'] != None:
                    default_service_product['audit_suggestion'] = self.clearZFC(
                        item['shjy'])
                else:
                    default_service_product['audit_suggestion'] = ''
                # 审核日期
                if 'shrq' in item and item['shrq'] != None:
                    default_service_product['audit_date'] = self.strtodate(
                        self.clearZFC(item['shrq']))
                else:
                    default_service_product['audit_date'] = ''
                # 封面图片
                if 'fmtp' in item and item['fmtp'] != None:
                    fmtp = self.get_url_upload_url(self.clearZFC(item['fmtp']))
                    if fmtp != '':
                        default_service_product['cover_photo'] = [fmtp]
                else:
                    default_service_product['cover_photo'] = []

                # 导入一个服务产品
                service_product_info = get_info(
                    default_service_product, self.session)

                # 替换创建时间
                if 'cjrq' in item and item['cjrq'] != None:
                    default_service_product['create_date'] = self.strtodate(
                        self.clearZFC(item['cjrq']))

                self.bill_manage_service.add_bill(OperationType.add.value,
                                                  TypeId.serviceProduct.value, service_product_info, service_product_table)

        return

    def import_excel_service_product(self, data):
        # 导入服务套餐
        pf = pd.DataFrame(data)
        pf.rename(columns={'机构名称': 'jgmc', '服务套餐主键': 'fwtczj', '服务套餐名字': 'fwtcmz', '服务方式': 'fwfs', '服务套餐描述': 'fwtcms', '服务套餐价格': 'fwtcjg', '有效期': 'yxq', '服务套餐销量': 'fwtcxl', '是否发布到APP': 'sffbdapp',
                           '服务项目内容': 'fwxmnr', '服务项目名称': 'fwxmmc', '服务项目价格': 'fwxmjg', '服务次数': 'fwcs', '创建日期': 'cjrq', '发布日期': 'fbrq', '失效日期': 'sxrq', '审核状态': 'shzt', '审核建议': 'shjy', '审核日期': 'shrq', '服务套餐封面图': 'fwtcfmt', '服务套餐图片': 'fwtctp'}, inplace=True)
        condition = dataframe_to_list(pf)

        _filter = MongoBillFilter()
        _filter.match_bill((C('bill_status') == 'valid'))\
            .project({'_id': 0})
        res = self.query(
            _filter, 'PT_Service_Product3')

        count = 0
        print(len(res))

        if len(res) > 0:
            for item in res:
                count = count + 1
                print(str(count))
                for itm in item['service_product']:
                    itm['remaining_times'] = item['remaining_times']

                def process_func(db):
                    update_data(db, 'PT_Service_Product3', item, {
                        'id': item['id'],
                        'bill_status': 'valid',
                    })
                process_db("127.0.0.1", 27017, 'IEC',
                           process_func, 'admin', '123456')
        return

        # 服务项目数据表
        service_item_table = 'PT_Service_Product'
        # 服务产品数据表
        service_product_table = 'PT_Service_Product'

        # 储存所有的
        temp_all = {}
        # 服务机构
        temp_jgmc = {}

        if len(condition) > 0:
            for item in condition:
                default_service_product = {
                    "name": "",
                    "introduce": "",
                    "remarks": "",
                    "state": 1,
                    "is_external": "1",
                    "is_recommend": "是",
                    "organization_id": "",
                    "service_product_type": [],
                    "picture_collection": "",
                    "additonal_contract_terms": "",
                    "service_product_introduce": "",
                    "is_service_item": "false",
                    "service_product": [],
                    "is_assignable": "1",
                    "remaining_times": "1",
                }

                # 以主键作为套餐的标准
                if 'fwtczj' in item and item['fwtczj'] != None:
                    fwtczj = self.clearZFC(item['fwtczj'])
                    if fwtczj not in temp_all:
                        # 新的一个套餐

                        # 判断所属机构
                        if 'jgmc' in item and item['jgmc'] != None:
                            jgmc = self.clearZFC(item['jgmc'])
                            if jgmc in temp_jgmc:
                                default_service_product['organization_id'] = temp_jgmc[jgmc]
                                # 赋值。下面生成服务产品就不用重复找了
                                item['organization_id'] = temp_jgmc[jgmc]
                            else:
                                _filter_jgmc = MongoBillFilter()
                                _filter_jgmc.match_bill((C('name') == jgmc))\
                                    .project({'_id': 0})
                                res_jgmc = self.query(
                                    _filter_jgmc, 'PT_User56test')

                                if len(res_jgmc) > 0:
                                    temp_jgmc[jgmc] = res_jgmc[0]['id']
                                    default_service_product['organization_id'] = res_jgmc[0]['id']
                                    # 赋值。下面生成服务产品就不用重复找了
                                    item['organization_id'] = res_jgmc[0]['id']

                        # 服务套餐图片
                        if 'fwtctp' in item and item['fwtctp'] != None:
                            fwxmtp = self.get_url_upload_url(
                                self.clearZFC(item['fwtctp']))
                            if fwxmtp != '':
                                default_service_product['picture_collection'] = [
                                    fwxmtp]
                        else:
                            default_service_product['picture_collection'] = []
                        # 名字
                        if 'fwtcmz' in item and item['fwtcmz'] != None:
                            default_service_product['name'] = self.clearZFC(
                                item['fwtcmz'])
                        else:
                            default_service_product['name'] = ''
                        # 描述
                        if 'fwtcms' in item and item['fwtcms'] != None:
                            default_service_product['introduce'] = self.clearZFC(
                                item['fwtcms'])
                        else:
                            default_service_product['introduce'] = ''
                        # 服务方式
                        if 'fwfs' in item and item['fwfs'] != '':
                            default_service_product['service_mode'] = self.clearZFC(
                                item['fwfs'])
                        else:
                            default_service_product['service_mode'] = ''
                        # 服务套餐价格
                        if 'fwtcjg' in item and item['fwfs'] != '':
                            default_service_product['service_package_price'] = self.clearZFC(
                                item['fwtcjg'])
                        else:
                            default_service_product['service_package_price'] = ''
                        # 有效期
                        if 'yxq' in item and item['yxq'] != '':
                            default_service_product['term_of_validity'] = self.clearZFC(
                                item['yxq'])
                        else:
                            default_service_product['term_of_validity'] = ''
                        # 是否发布到app
                        if 'sffbdapp' in item and item['sffbdapp'] != '':
                            default_service_product['is_app'] = self.clearZFC(
                                item['sffbdapp'])
                        else:
                            default_service_product['is_app'] = ''
                        # 服务次数
                        if 'fwcs' in item and item['fwcs'] != '':
                            default_service_product['remaining_times'] = self.clearZFC(
                                item['fwcs'])
                        else:
                            default_service_product['remaining_times'] = '1'
                        # 销售量
                        if 'fwtcxl' in item and item['fwtcxl'] != None:
                            default_service_product['sales_volume'] = self.clearZFC(
                                item['fwtcxl'])
                        else:
                            default_service_product['sales_volume'] = ''
                        # 审核状态
                        if 'shzt' in item and item['shzt'] != None:
                            default_service_product['audit_status'] = self.clearZFC(
                                item['shzt'])
                        else:
                            default_service_product['audit_status'] = ''
                        # 审核建议
                        if 'shjy' in item and item['shjy'] != None:
                            default_service_product['audit_suggestion'] = self.clearZFC(
                                item['shjy'])
                        else:
                            default_service_product['audit_suggestion'] = ''
                        # 审核日期
                        if 'shrq' in item and item['shrq'] != None:
                            default_service_product['audit_date'] = self.strtodate(
                                self.clearZFC(item['shrq']))
                        else:
                            default_service_product['audit_date'] = ''
                        # 封面图片
                        if 'fwtcfm' in item and item['fwtcfm'] != None:
                            fwtcfm = self.get_url_upload_url(
                                self.clearZFC(item['fwtcfm']))
                            if fwtcfm != '':
                                default_service_product['cover_photo'] = [
                                    fwtcfm]
                        else:
                            default_service_product['cover_photo'] = []

                        # 发布日期
                        if 'fbrq' in item and item['fbrq'] != None:
                            default_service_product['release_date'] = self.strtodate(
                                self.clearZFC(item['fbrq']))
                        else:
                            default_service_product['release_date'] = ''
                        # 失效日期
                        if 'sxrq' in item and item['sxrq'] != None:
                            default_service_product['invalid_date'] = self.strtodate(
                                self.clearZFC(item['sxrq']))
                        else:
                            default_service_product['invalid_date'] = ''
                        # 服务项目
                        if 'fwxmmc' in item and item['fwxmmc'] != None:
                            fwxmmc = self.clearZFC(item['fwxmmc'])
                            # 判断机构及服务项目名称
                            _filter_fwxm = MongoBillFilter()
                            _filter_fwxm.match_bill((C('name') == fwxmmc) & (C('is_service_item') == "true") & (C('organization_id') == default_service_product['organization_id']))\
                                .project({'_id': 0})
                            res_fwxm = self.query(
                                _filter_fwxm, service_item_table)
                            default_service_product['service_product'].append({
                                'product_id': res_fwxm[0]['id']
                            })

                        # 导入一个服务产品
                        service_product_info = get_info(
                            default_service_product, self.session)

                        # 替换创建时间
                        if 'cjrq' in item and item['cjrq'] != None:
                            default_service_product['create_date'] = self.strtodate(
                                self.clearZFC(item['cjrq']))

                        temp_all[fwtczj] = service_product_info
                    else:
                        # 已经有直接添加一个服务选项id

                        # 服务项目
                        # 不允许这个为空，如果有错就直接抛出来
                        fwxmmc = self.clearZFC(item['fwxmmc'])
                        # 判断机构及服务项目名称
                        _filter_fwxm = MongoBillFilter()
                        _filter_fwxm.match_bill((C('name') == fwxmmc) & (C('is_service_item') == "true") & (C('organization_id') == temp_all[fwtczj]['organization_id']))\
                            .project({'_id': 0})
                        res_fwxm = self.query(
                            _filter_fwxm, service_item_table)
                        temp_all[fwtczj]['service_product'].append({
                            'product_id': res_fwxm[0]['id']
                        })

            # 遍历存起来
            for itm in temp_all.values():
                self.bill_manage_service.add_bill(OperationType.add.value,
                                                  TypeId.serviceProduct.value, itm, service_product_table)

    # 导入双鸭山人员
    def import_excel_sys_person(self, data):
        pf = pd.DataFrame(data)
        pf.rename(columns={'社区名称': 'area_name', '人员姓名': 'name', '性别': 'sex', '出生日期': 'date_birth', '身份证号': 'id_card', '特殊人群': 'special', '残疾人等级': 'broken_level', '社保类型': 'social_type', '户籍性质': 'place_type', '人户分离': 'person_place_leave', '与户主关系': 'relation', '户籍地址': 'address', '户口迁入地': 'place_from', '迁入时间': 'come_date', '房屋类型': 'home_type',
                           '工作单位': 'work_place', '固定电话': 'phone', '移动电话': 'telephone', '民族': 'nation', '政治面貌': 'political_outlook', '学历': 'education', '毕业院校': 'graduate_school', '毕业时间': 'graduate_date', '职业类型': 'work_type', '婚姻状况': 'marital_status', '宗教信仰': 'religious_belief', '兴趣爱好': 'hobby', '备注': 'remark', '长者类型': 'personnel_classification'}, inplace=True)
        condition = dataframe_to_list(pf)

        out_keys = ['name', 'id_card']
        in_keys = ['name', 'id_card', 'sex', 'telephone', 'education',
                   'nation', 'address', 'political_outlook', 'personnel_classification']
        other_keys = ['special', 'broken_level', 'social_type', 'place_type', 'person_place_leave', 'relation', 'place_from',
                      'home_type', 'work_place', 'phone', 'hobby', 'graduate_school', 'work_type', 'marital_status', 'religious_belief', 'hobby']
        # 默认游客的角色ID
        default_role_id = '8ed260f6-e355-11e9-875e-a0a4c57e9ebe'
        # 默认组织ID
        default_organization_id = 'c7e1ac7a-0d08-11ea-aebe-a0a4c57e8195'

        arr = []
        arr2 = []

        count = 0

        if len(condition) > 0:
            for item in condition:
                count = count + 1
                newData = {
                    'id_card_type': '身份证',
                    'personnel_type': '1',
                    'personnel_info': {
                        'personnel_category': '长者',
                        'role_id': default_role_id,
                        'other_params': {

                        }
                    },
                    'organization_id': default_organization_id,
                    'login_info': [],
                }
                otherParam = {}
                otherParam = self._setValues(other_keys, item, otherParam)
                newData = self._setValues(out_keys, item, newData)
                newData['personnel_info'] = self._setValues(
                    in_keys, item, newData['personnel_info'])
                newData['personnel_info']['other_params'] = otherParam

                _filter_code = MongoBillFilter()
                _filter_code.match_bill((C('name') == item['area_name']))\
                    .project({'_id': 0})
                area_info = self.query(
                    _filter_code, 'PT_Area_SYS')

                if len(area_info) > 0:
                    newData['admin_area_id'] = area_info[0]['id']

                if 'date_birth' in item and item['date_birth'] != None:
                    date_birth = self.clearZFC(item['date_birth'])
                    newData['personnel_info']['date_birth'] = self.strtodate(
                        date_birth)
                if 'come_date' in item and item['come_date'] != None:
                    come_date = self.clearZFC(item['come_date'])
                    newData['personnel_info']['other_params']['come_date'] = self.strtodate(
                        come_date)
                if 'graduate_date' in item and item['graduate_date'] != None:
                    graduate_date = self.clearZFC(item['graduate_date'])
                    newData['personnel_info']['other_params']['graduate_date'] = self.strtodate(
                        graduate_date)

                data = get_info(newData, self.session)

                # 建立四个账户表
                financialAccountsResult = self._getFinancialAccount(data)

                data = financialAccountsResult[0]

                tables = ['Z_PT_User_SYS', 'Z_PT_Financial_Account_SYS']
                datas = [[data], financialAccountsResult[1]]

                set_role = {'role_id': default_role_id,
                            'principal_account_id': data['id'], 'role_of_account_id': data['organization_id']}
                set_role_data = get_info(set_role, self.session)
                datas.append(set_role_data)
                tables.append('Z_PT_Set_Role_SYS')
                try:
                    self.bill_manage_service.add_bill(OperationType.add.value,
                                                      TypeId.user.value, datas, tables)
                except Exception as e:
                    print(e)

                print('人员表第'+str(count)+'个')

    def import_excel_personnel3(self, data):
        pf = pd.DataFrame(data)
        pf.rename(columns={'status': 'zszt', '区域编号': 'qybh', '行政区划': 'xzqh', '人员姓名': 'ryxm', '性别': 'xb', '出生日期': 'csrq', '身份证号': 'sfzh', '所属组织': 'sszz', '特殊人群': 'tsrq', '残疾人等级': 'cjrdj', '社保类型': 'sblx', '户籍性质': 'hjxz', '人户分离': 'rhfl', '与户主关系': 'yhzgx', '户籍地址': 'hjdz',
                           '户口迁入地': 'hkqrd', '迁入时间': 'qrsj', '房屋类型': 'fwlx', '工作单位': 'gzdw', '固定电话': 'gddh', '移动电话': 'yddh', '民族': 'mz', '政治面貌': 'zzmm', '学历': 'xl', '毕业院校': 'byyx', '毕业时间': 'bysj', '职业类别': 'zylb', '婚姻状态': 'hyzt', '宗教信仰': 'zjxy', '兴趣爱好': 'xqah', '备注': 'bz', '长者类型': 'zzlx'}, inplace=True)
        # pf = pf.drop(['age', 'replacement_amount', 'total_amount', 'title'], axis=1)
        condition = dataframe_to_list(pf)

        # 组织机构的缓存数组
        tempZZ = {}
        # 区域编号的缓存数组
        tempQH = {}
        # 长者类型的缓存数组
        tempLX = {}

        usertb = 'PT_User'
        financialtb = 'PT_Financial_Account'
        setroletb = 'PT_Set_Role'
        area_str = ''

        # 默认佛山市区域ID
        default_area_id = 'da1e4201-70c5-407a-a088-c3a8cc91fef5'
        # 默认游客的角色ID
        default_role_id = '8ed260f6-e355-11e9-875e-a0a4c57e9ebe'
        # 默认平台的组织ID
        default_organization_id = '7e7e2fec-d91d-11e9-8e1d-983b8f0bcd67'

        if len(condition) > 0:

            if condition[0]['ryxm'] == '邱月爱':
                area_str = 'cc'
                area_cn = '禅城'
            elif condition[0]['ryxm'] == '岑美梅':
                area_str = 'dl'
                area_cn = '大沥'
            elif condition[0]['ryxm'] == '���惠兴':
                area_str = 'dz'
                area_cn = '丹灶'
            elif condition[0]['ryxm'] == '黎丹丹':
                area_str = 'gc'
                area_cn = '桂城'
            elif condition[0]['ryxm'] == '李金弟':
                area_str = 'jj'
                area_cn = '九江'
            elif condition[0]['ryxm'] == '吴玉兰':
                area_str = 'na'
                area_cn = '空编号'
            elif condition[0]['ryxm'] == '柯少君':
                area_str = 'ls'
                area_cn = '里水'
            elif condition[0]['ryxm'] == '揭绍明':
                area_str = 'ot'
                area_cn = '其他'
            elif condition[0]['ryxm'] == '刘惠英':
                area_str = 'xq'
                area_cn = '西樵'
            elif condition[0]['ryxm'] == '刘巨亨':
                area_str = 'ss'
                area_cn = '狮山'

            usertb = usertb + '_' + area_str
            financialtb = financialtb + '_' + area_str
            setroletb = setroletb + '_' + area_str

            count = 0

            for item in condition:
                # 导入人员
                newData = {
                    "name": "",
                    "personnel_type": "1",
                    "admin_area_id": default_area_id,
                    "town": "",
                    "id_card_type": "身份证",
                    "id_card": "",
                    "organization_id": default_organization_id,
                    "reg_date": get_cur_time(),
                    "login_info": [],
                    "personnel_info": {
                        "name": "",
                        "id_card": "",
                        "date_birth": "",
                        "sex": "",
                        "telephone": "",
                        "remarks": "",
                        "personnel_category": "长者",
                        "address": "",
                        "role_id": default_role_id,
                        "is_alive": "",
                        "personnel_classification": "",
                    }
                }
                # 姓名
                if 'ryxm' in item and item['ryxm'] != None:
                    newData['name'] = item['ryxm']
                    newData['personnel_info']['name'] = item['ryxm']
                # 身份证号
                if 'sfzh' in item and item['sfzh'] != None:
                    newData['id_card'] = str(item['sfzh'])
                    newData['personnel_info']['id_card'] = str(item['sfzh'])
                # 性别
                if 'xb' in item and item['xb'] != None:
                    newData['personnel_info']['sex'] = '男' if item['xb'] == 'Male' else '女'
                # 地址
                if 'hjdz' in item and item['hjdz'] != None:
                    newData['personnel_info']['address'] = item['hjdz']
                # 电话
                if 'yddh' in item and item['yddh'] != None:
                    newData['personnel_info']['telephone'] = str(item['yddh'])
                # 在世状态
                if 'zszt' in item and item['zszt'] != None:
                    newData['personnel_info']['is_alive'] = True if item['zszt'] == 'Normal' else False
                # 长者类型
                if 'zzlx' in item and item['zzlx'] != None:
                    newData['personnel_info']['personnel_classification'] = item['zzlx']

                # 所属组织
                if 'sszz' in item and item['sszz'] != None:
                    if item['sszz'] in tempZZ:
                        print('已有所属组织，无需重复获取')
                        newData['organization_id'] = tempZZ[item['sszz']]
                    else:
                        _filter_user = MongoBillFilter()
                        _filter_user.match_bill((C('name') == item['sszz']))\
                            .project({'_id': 0})
                        organization_info = self.query(
                            _filter_user, 'PT_User56')

                        if len(organization_info) > 0:
                            tempZZ[item['sszz']] = organization_info[0]['id']
                            newData['organization_id'] = organization_info[0]['id']

                # 区域编号
                if 'qybh' in item and item['qybh'] != None:
                    qhbh = str(item['qybh'])
                    if qhbh in tempQH:
                        print('已有区域编号，无需重复获取')
                        newData['admin_area_id'] = tempQH[qhbh]
                    else:
                        _filter_code = MongoBillFilter()
                        _filter_code.match_bill((C('code') == qhbh))\
                            .project({'_id': 0})
                        area_info = self.query(
                            _filter_code, 'PT_Administration_Division56')

                        if len(area_info) > 0:
                            tempQH[qhbh] = area_info[0]['id']
                            newData['admin_area_id'] = area_info[0]['id']

                # 人员类别
                if 'zzlx' in item and item['zzlx'] != None:
                    zzlx = str(item['zzlx'])
                    if zzlx in tempLX:
                        print('已有长者类型，无需重复获取')
                        newData['personnel_info']['personnel_classification'] = tempLX[zzlx]
                    else:
                        _filter_leixing = MongoBillFilter()
                        _filter_leixing.match_bill((C('name') == zzlx))\
                            .project({'_id': 0})
                        lx_info = self.query(
                            _filter_leixing, 'PT_Personnel_Classification')

                        if len(lx_info) > 0:
                            tempQH[zzlx] = lx_info[0]['id']
                            newData['personnel_info']['personnel_classification'] = lx_info[0]['id']

                # 生日
                if 'csrq' in item and item['csrq'] != None:
                    newData['personnel_info']['date_birth'] = self.date_to_format(
                        item['csrq'])

                data = get_info(newData, self.session)

                # 新增账户信息（1个机构储蓄、1个app储蓄、1个真实账户、1个补贴账户）
                # 机构储蓄对象
                org_account_data = FinancialAccountObject(
                    AccountType.account_recharg_wel, data['id'], data['organization_id'], AccountStatus.normal, 1, AccountType.account_recharg_wel, {}, 0)
                org_account = get_info(
                    org_account_data.to_dict(), self.session)
                # app储蓄对象
                app_account_data = FinancialAccountObject(
                    AccountType.account_recharg_app, data['id'], plat_id, AccountStatus.normal, 2, AccountType.account_recharg_app, {}, 0)
                app_account = get_info(
                    app_account_data.to_dict(), self.session)
                # 真实账户对象
                real_account_data = FinancialAccountObject(
                    AccountType.account_real, data['id'], None, AccountStatus.normal, 3, AccountType.account_real, {}, 0)
                real_account = get_info(
                    real_account_data.to_dict(), self.session)
                # 补贴账户对象
                subsidy_account_data = FinancialAccountObject(
                    AccountType.account_subsidy, data['id'], None, AccountStatus.normal, 4, AccountType.account_subsidy, {}, 0)
                subsidy_account = get_info(
                    subsidy_account_data.to_dict(), self.session)
                tables = [usertb, financialtb]
                datas = [[data], [org_account, app_account,
                                  real_account, subsidy_account]]

                set_role = {'role_id': default_role_id,
                            'principal_account_id': data['id'], 'role_of_account_id': data['organization_id']}
                set_role_data = get_info(set_role, self.session)
                datas.append(set_role_data)
                tables.append(setroletb)

                count = count + 1
                print('开始导入第' + str((count)) + '个' + '_' +
                      str(area_cn) + '_' + str(data['name']))
                try:
                    self.bill_manage_service.add_bill(OperationType.add.value,
                                                      TypeId.user.value, datas, tables)
                except Exception as e:
                    print(e)
        return '导入成功'

    def clearZFC(self, string):
        # 清除excel可能自带的换行符和空格符
        if string == None:
            return ''
        return str(string).strip().replace(
            '\r', '').replace('\n', '').replace('\t', '')

    # 设置机构类型
    def _setPersonnelCategory(self, value, defaultValue=''):
        if value != None:
            value = self.clearZFC(value)
            if value in JiGouLeiBie.__members__:
                return JiGouLeiBie[value].value
        if defaultValue != '':
            return defaultValue
        return ''

    # 设置时间格式
    def _setTimeFormat(self, value, defaultValue=''):
        if value != None:
            return self.strtodate(self.clearZFC(value))
        if defaultValue != '':
            return defaultValue
        return ''

    # 判断所属行政编号
    def _getAdministratorArea(self, value, temp={}, defaultValue=''):
        if value != None:
            value = self.clearZFC(value)
            if value in temp:
                return temp[value]
            _filter = MongoBillFilter()
            _filter.match_bill((C('name') == value))\
                .project({'_id': 0})
            res = self.query(
                _filter, 'PT_Administration_Division56')

            if len(res) > 0:
                return res[0]['id']
        if defaultValue != '':
            return defaultValue
        return ''

    # 根据产品名字和组织机构ID找出对应的产品详情
    def _getProductInfo(self, value, temp={}, organization_id=''):
        if value != None:
            value = self.clearZFC(value)
            if value in temp:
                return temp[value]
            _filter = MongoBillFilter()
            _filter.match_bill((C('name') == value) & (C('organization_id') == organization_id))\
                .project({'_id': 0})
            res = self.query(
                _filter, 'PT_Service_Product')

            if len(res) > 0:
                return res
        return ''

    # 根据身份证号获取人员id
    def _getUserId(self, value, temp={}, defaultValue='', searchType='id_card', organization_id='', userType=''):
        if searchType == '':
            searchType = 'id_card'
        if value != None:
            value = self.clearZFC(value)
            if value in temp:
                return temp[value]
            _filter = MongoBillFilter()
            if searchType == 'name':
                _filter.match_bill((C('name') == value))
            else:
                _filter.match_bill((C('personnel_info.id_card') == value))
            # 组织机构
            if organization_id != '':
                _filter.match_bill((C('organization_id') == organization_id))
            # 工作人员或者长者
            if userType != '':
                if userType == 'worker':
                    _filter.match_bill(
                        (C('personnel_info.personnel_category') == '工作人员'))
                elif userType == 'elder':
                    _filter.match_bill(
                        (C('personnel_info.personnel_category') == '长者'))
            _filter.match_bill((C('personnel_type') == '1'))
            _filter.project({'_id': 0})
            res = self.query(
                _filter, 'PT_User')
            if len(res) > 0:
                return res[0]['id']
        if defaultValue != '':
            return defaultValue
        return ''

    # 根据组织编号获取机构id
    def _getOrganizationId(self, value, temp={}, defaultValue='', searchType='code'):
        if value != None:
            value = self.clearZFC(value)
            if value in temp:
                return temp[value]
            _filter = MongoBillFilter()
            if searchType == 'name':
                _filter.match_bill((C('name') == value) &
                                   (C('personnel_type') == '2'))
            else:
                _filter.match_bill((C('organization_info.code') == value) & (
                    C('personnel_type') == '2'))
            _filter.project({'_id': 0})
            res = self.query(
                _filter, 'PT_User')
            if len(res) > 0:
                return res[0]['id']
        if defaultValue != '':
            return defaultValue
        return ''

    # 通过循环来填充数据
    def _setValues(self, keys, item, datas):
        for i in range(0, len(keys)):
            key = keys[i]
            if key in item and item[key] != None:
                value = self.clearZFC(item[key])
                # 如果旧数据有，而新数据没有，则跳过不替换
                if (key in datas and datas[key] != '' and value == '') or (key not in datas and value == ''):
                    continue
                datas[key] = self._getMeiJuValue(key, value)
        return datas

    # 判断是否是枚举类的数据，如果是就从枚举类上拿
    def _getMeiJuValue(self, key, value):
        MJ_ARRAY = ['sex', 'post', 'education', 'worker_category', 'create_method', 'buy_type', 'source_type', 'pay_status',
                    'periodicity', 'record_status', 'visit_status', 'service_satisfaction', 'checkin_status', 'sign_type', 'bed_status']
        if key in MJ_ARRAY:
            if key == 'sex' and value in Sex.__members__:
                return Sex[value].value
            elif key == 'post' and value in Post.__members__:
                return Post[value].value
            elif key == 'education' and value in XueLiZiZhi.__members__:
                return XueLiZiZhi[value].value
            elif key == 'worker_category' and value in WorkerCategory.__members__:
                return WorkerCategory[value].value
            elif key == 'create_method' and value in CreateMethod.__members__:
                return CreateMethod[value].value
            elif key == 'buy_type' and value in BuyType.__members__:
                return BuyType[value].value
            elif key == 'source_type' and value in SourceType.__members__:
                return SourceType[value].value
            elif key == 'pay_status' and value in PayStatus.__members__:
                return PayStatus[value].value
            elif key == 'periodicity' and value in Cycle.__members__:
                return Cycle[value].value
            elif key == 'record_status' and value in RecordStatus.__members__:
                return RecordStatus[value].value
            elif key == 'visit_status' and value in VisitStatus.__members__:
                return VisitStatus[value].value
            elif key == 'service_satisfaction' and value in ServiceEvaluation.__members__:
                return ServiceEvaluation[value].value
            elif key == 'checkin_status' and value in CheckInStatus2.__members__:
                return CheckInStatus2[value].value
            elif key == 'sign_type' and value in SignType.__members__:
                return SignType[value].value
            elif key == 'bed_status' and value in BedStatus.__members__:
                return BedStatus[value].value
        return value

    # 获取默认数据
    def _getDefaultValue(self, key, item, defaultValue=''):
        if key not in item:
            if defaultValue != '':
                return defaultValue
            return ''
        return item[key]

    def import_excel_organization(self, data):
        # 导入机构
        pf = pd.DataFrame(data)
        pf.rename(columns={'名称': 'name', '编号': 'code', '地址': 'address', '经度': 'lon', '维度': 'lat', '法人': 'legal_person',
                           '联系电话': 'telephone', '机构性质': 'organization_nature', '行政区域（编号）': 'admin_area_id', '上级机构（编号）': 'sjjgbh', '星级': 'star_level', '简介': 'description', '备注': 'comment', '机构成立时间': 'reg_date', '类型': 'personnel_category'}, inplace=True)
        condition = dataframe_to_list(pf)

        usertb = 'PT_User_Org'
        financialtb = 'ZZPT_Financial_Account'

        temp_area = {}

        # 默认佛山市区域ID
        default_area_id = 'da1e4201-70c5-407a-a088-c3a8cc91fef5'

        # 找出有没有
        if len(condition) > 0:
            for item in condition:

                # 这里根据编号做判断
                if 'code' in item and item['code'] != None:
                    code = self.clearZFC(item['code'])
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('organization_info.code') == code))\
                        .project({'_id': 0})
                    res = self.query(
                        _filter, usertb)
                else:
                    # 没有code的话，就用名字和幸福院类型作为判断依据
                    name = self.clearZFC(item['name'])
                    personnel_category = self.clearZFC(
                        item['personnel_category'])
                    personnel_category = JiGouLeiBie[personnel_category].value
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('name') == name) & (C('organization_info.personnel_category') == personnel_category))\
                        .project({'_id': 0})
                    res = self.query(
                        _filter, usertb)

                out_org = ['name']

                in_org = ['code', 'telephone', 'lon', 'lat', 'address',
                          'comment', 'legal_person', 'organization_nature', 'description', 'star_level']

                # 有数据，用update_one更改修改后的数据
                if len(res) > 0:

                    # 判断organization_info里的数据是否一致
                    res[0] = self._setValues(out_org, item, res[0])

                    # 判断organization_info里的数据是否一致
                    res[0]['organization_info'] = self._setValues(
                        in_org, item, res[0]['organization_info'])

                    # 成立时间
                    if 'reg_date' in item:
                        defaultValue = self._getDefaultValue(
                            'reg_date', res[0])
                        res[0]['reg_date'] = self._setTimeFormat(
                            item['reg_date'], defaultValue)

                    # 机构类型
                    if 'personnel_category' in item:
                        res[0]['organization_info']['personnel_category'] = self._setPersonnelCategory(
                            item['personnel_category'])

                    # 判断所属行政编号
                    if 'admin_area_id' in item:
                        defaultValue = self._getDefaultValue(
                            'admin_area_id', res[0], default_area_id)
                        res[0]['admin_area_id'] = self._getAdministratorArea(
                            item['admin_area_id'], temp_area, defaultValue)
                        temp_area[self.clearZFC(
                            item['admin_area_id'])] = res[0]['admin_area_id']

                    # 导入标识
                    res[0] = self.setImportDate(res[0], '20200108')

                    def process_func(db):
                        update_data(db, usertb, res[0], {
                            'id': res[0]['id'],
                            'bill_status': 'valid',
                        })
                    process_db("127.0.0.1", 27017, 'IEC',
                               process_func, 'admin', '123456')
                else:
                    # 新增的机构数据
                    new_org = {
                        "name": "",
                        "admin_area_id": "da1e4201-70c5-407a-a088-c3a8cc91fef5",
                        "personnel_type": "2",
                        "town": "",
                        "organization_info": {
                            "code": "",
                            "telephone": "",
                            "organization_nature": "",
                            "lon": '',
                            "lat": '',
                            "personnel_category": "",
                            "legal_person": "",
                            "address": "",
                            "star_level": "",
                            "description": "",
                            "reg_date": "",
                            "super_org_id": "7e7e2fec-d91d-11e9-8e1d-983b8f0bcd67",
                            "picture_list": []
                        },
                        "qualification_info": [],
                    }
                    # 新增

                    # 判断organization_info里的数据是否一致
                    new_org = self._setValues(out_org, item, new_org)

                    # 判断organization_info里的数据是否一致
                    new_org['organization_info'] = self._setValues(
                        in_org, item, new_org['organization_info'])

                    # 成立时间
                    if 'reg_date' in item:
                        new_org['reg_date'] = self._setTimeFormat(
                            item['reg_date'])

                    # 机构类型
                    if 'personnel_category' in item:
                        new_org['organization_info']['personnel_category'] = self._setPersonnelCategory(
                            item['personnel_category'])

                    # 判断所属行政编号
                    if 'admin_area_id' in item:
                        new_org['admin_area_id'] = self._getAdministratorArea(
                            item['admin_area_id'], temp_area)
                        temp_area[self.clearZFC(
                            item['admin_area_id'])] = new_org['admin_area_id']

                    # 导入标识
                    new_org = self.setImportDate(new_org, '20200108')

                    data = get_info(new_org, self.session)

                    # 建立账户表
                    financialAccountsResult = self._getFinancialAccount(data)

                    if financialAccountsResult != False:
                        # 主数据，账户表
                        datas = [financialAccountsResult[0],
                                 financialAccountsResult[1]]

                        try:
                            self.bill_manage_service.add_bill(OperationType.add.value,
                                                              TypeId.user.value, datas, [usertb, financialtb])
                        except Exception as e:
                            print(e)
                    else:
                        # 主数据
                        try:
                            self.bill_manage_service.add_bill(OperationType.add.value,
                                                              TypeId.user.value, data, usertb)
                        except Exception as e:
                            print(e)
        return '导入成功'

    # 导入资讯
    def import_excel_article(self, data):
        pf = pd.DataFrame(data)
        pf.rename(columns={'标题': 'title', '内容': 'content', '封面图片': 'photo', '创建日期': 'create_date', '点赞数量': 'like', '发布日期': 'issue_date', '发布状态': 'status', '是否封存': 'is_lock', '分享数': 'share',
                           '是否置顶': 'is_top', '创建人': 'author'}, inplace=True)
        condition = dataframe_to_list(pf)

        saveTable = 'ZPT_Article'

        keys = ['title', 'content', 'like', 'share', 'author']

        # 默认平台的组织ID，导入的都是一百件大健康的
        default_organization_id = '87e94ca4-26ff-11ea-9fec-a0a4c57e9ebe'
        default_promulgator_id = '23b3630a-d92c-11e9-8b9a-983b8f0bcd67'

        temp_organization = {}
        temp_worker = {}

        arr = []

        count = 0

        if len(condition) > 0:

            for item in condition:
                count = count + 1
                # 新增资讯数据
                newData = {}

                # 补充原数据
                newData = self._setValues(keys, item, newData)

                # 找出所属工作人员
                if 'author' in item and item['author'] != None:
                    author = self.clearZFC(item['author'])
                    if author in temp_worker:
                        newData['promulgator_id'] = temp_worker[author]
                    else:
                        _filter = MongoBillFilter()
                        _filter.match_bill((C('organization_id') == default_organization_id) & (C('personnel_info.personnel_category') == '工作人员') & (C('name') == author))\
                            .project({'_id': 0})
                        res = self.query(
                            _filter, 'PT_User')

                        if len(res) > 0:
                            newData['promulgator_id'] = res[0]['id']
                            temp_worker[author] = res[0]['id']

                # 置顶
                if 'is_top' in item and item['is_top'] != None:
                    is_top = self.clearZFC(item['is_top'])
                    if is_top == '1':
                        newData['showing_status'] = '置顶'
                    elif is_top == '0':
                        newData['showing_status'] = '首页'

                # 状态
                if 'status' in item and item['status'] != None:
                    status = self.clearZFC(item['status'])
                    if status == 'Released':
                        newData['old_status'] = '已发布'
                    elif status == 'UnReleased':
                        newData['old_status'] = '未发布'

                # 照片
                if 'photo' in item and item['photo'] != None:
                    photo = self.get_url_upload_url(
                        self.clearZFC(item['photo']), 'articlePhoto')
                    if photo != '':
                        newData['photo'] = [photo]
                else:
                    newData['photo'] = []

                # 发布时间
                if 'issue_date' in item and item['issue_date'] != None:
                    issue_date = self.clearZFC(item['issue_date'])
                    newData['issue_date'] = self._setTimeFormat(issue_date)
                # 所属机构
                if 'organization_code' in item and item['organization_code'] != None:
                    organization_code = self.clearZFC(
                        item['organization_code'])
                    organization_id = self._getOrganizationId(
                        organization_code, temp_organization, default_organization_id)
                    newData['organization_id'] = organization_id
                    temp_organization[organization_code] = organization_id
                else:
                    newData['organization_id'] = default_organization_id
                # 导入标识
                newData = self.setImportDate(newData, '20200110')

                newData['type_id'] = 'f1643398-cf91-11e9-9aab-000c29acd33e'
                newData['status'] = 'audit_success'

                data = get_info(newData, self.session)

                # 发布时间
                if 'create_date' in item and item['create_date'] != None:
                    create_date = self.clearZFC(item['create_date'])
                    newData['create_date'] = self._setTimeFormat(create_date)

                data['GUID'] = str(uuid.uuid1())
                data['version'] = 1
                data['bill_status'] = 'valid'
                data['valid_bill_id'] = str(uuid.uuid1())
                data['bill_operator'] = '23b3630a-d92c-11e9-8b9a-983b8f0bcd67'

                print('资讯表第'+str(count)+'个')

                arr.append(data)

        def process_func(db):
            insert_many_data(db, saveTable, arr)
        process_db(self.db_addr, self.db_port, self.db_name,
                   process_func, self.db_user, self.db_pwd)

    # 导入服务订单
    def import_excel_service_order(self, data):
        pf = pd.DataFrame(data)
        pf.rename(columns={'订单主键': 'order_primary_id', '订单编号': 'order_code', '预约日期': 'order_date', '创建方法': 'create_method', '服务类别': 'service_type', '付款状态': 'pay_status', '付款方法': 'buy_type', '订单项来源类型': 'source_type', '社工局': 'social_organization',
                           '服务类型': 'service_mode', '创建人': 'creator', '长者名称': 'name', '长者身份证': 'id_card', '数量': 'count', '价格': 'amout', '服务周期': 'periodicity', '服务次数': 'all_times', '服务机构名字': 'organization_name', '服务项目名称': 'service_option_name', '服务内容': "remark"}, inplace=True)
        condition = dataframe_to_list(pf)

        serviceOrderTable = 'ZPT_Service_Order'

        keys = ['create_method', 'service_mode', 'pay_status', 'amout', 'source_type',
                'periodicity', 'buy_type', 'order_code', 'order_primary_id', 'remark']

        # 默认平台的组织ID
        default_organization_id = '7e7e2fec-d91d-11e9-8e1d-983b8f0bcd67'

        temp_organization = {}
        temp_user = {}
        temp_creator = {}
        temp_product = {}

        arr = []

        count = 0

        if len(condition) > 0:
            for item in condition:

                count = count + 1

                newData = {}

                # 补充原数据
                newData = self._setValues(keys, item, newData)

                # 服务日期和预约日期一样
                if 'order_date' in item and item['order_date'] != None:
                    order_date = self.strtodate(
                        self.clearZFC(item['order_date']))
                    newData['order_date'] = order_date
                    newData['service_date'] = order_date

                # 所属机构
                if 'organization_name' in item and item['organization_name'] != None:
                    organization_name = self.clearZFC(
                        item['organization_name'])

                    organization_id = self._getOrganizationId(
                        organization_name, temp_organization, default_organization_id, 'name')
                    newData['organization_id'] = organization_id
                    temp_organization[organization_name] = organization_id
                else:
                    newData['organization_id'] = default_organization_id
                    temp_organization[organization_name] = default_organization_id

                # 根据服务产品名字和组织机构ID查询对应的ID
                if 'service_option_name' in item and item['service_option_name'] != None:
                    service_option_name = self.clearZFC(
                        item['service_option_name'])
                    product_info = self._getProductInfo(
                        service_option_name, temp_product, newData['organization_id'])
                    temp_product[service_option_name] = product_info

                    if product_info != '':
                        # 构建数据的detail
                        # 因为导入的excel都是单服务项目，所以这里没有做循环
                        newData['detail'] = []
                        newData['detail'].append({
                            "product_id": product_info[0]['id'],
                            "product_name": service_option_name,
                            "valuation_formula": product_info[0]['valuation_formula'],
                            "service_option": product_info[0]['service_option'],
                            "is_assignable": "1",
                            "count": item['count'],
                            "all_times": item['all_times']
                        })

                        newData['origin_product'] = {
                            "product_id": product_info[0]['id'],
                            "product_name": service_option_name,
                        }

                # 购买者
                if 'id_card' in item and item['id_card'] != None:
                    id_card = self.clearZFC(item['id_card'])
                    purchaser_id = self._getUserId(id_card, temp_user)
                    if purchaser_id != '':
                        newData['purchaser_id'] = purchaser_id
                    temp_user[id_card] = purchaser_id

                # 创建人
                if 'creator' in item and item['creator'] != None:
                    creator = self.clearZFC(item['creator'])
                    creator = self._getUserId(
                        creator, temp_creator, '', 'name', newData['organization_id'])
                    if creator != '':
                        newData['creator'] = creator
                    temp_creator[creator] = creator

                # 先付费
                newData['is_paying_first'] = '1'
                # 已分派
                newData['order_state'] = '已分派'
                # 转化为数字类型
                newData['amout'] = float(newData['amout'])
                # 提供商暂时用组织机构ID
                newData['service_provider_id'] = newData['organization_id']

                # 导入标识
                newData = self.setImportDate(
                    newData, '20200113')

                data = get_info(newData, self.session)

                data['GUID'] = str(uuid.uuid1())
                data['version'] = 1
                data['bill_status'] = 'valid'
                data['valid_bill_id'] = str(uuid.uuid1())
                data['bill_operator'] = '23b3630a-d92c-11e9-8b9a-983b8f0bcd67'

                print('服务订单第'+str(count)+'个')

                arr.append(data)

        def process_func(db):
            insert_many_data(db, serviceOrderTable, arr)
        process_db(self.db_addr, self.db_port, self.db_name,
                   process_func, self.db_user, self.db_pwd)

    # 导入床位信息
    def import_excel_bed(self, data):
        pf = pd.DataFrame(data)
        pf.rename(columns={'机构主键': 'organization_code', '所属区域': 'zone_name', '大楼名称': 'building_name', '房号': 'room_name', '楼层': 'floor_name', '床位主键': 'bed_primary_id', '床号': 'bed_name', '床位状态': 'bed_status', '床位费用': 'bed_price',
                           '入住/退住': 'checkin_status', '每月费用': 'price_everymonth', '入住/退住日期': 'checkin_date', '处理日期': 'create_date', '结束日期': 'end_date', '长者名称': 'elder_name', '长者身份证': 'id_card', '签入类型': 'sign_type', '一次性费用': 'price_onetimes'}, inplace=True)
        condition = dataframe_to_list(pf)

        # 已经有了，就补充信息
        keys = ['bed_status', 'bed_price', 'checkin_status', 'price_everymonth',
                'elder_name', 'id_card', 'sign_type', 'price_onetimes']

        # 床位表
        bedTable = 'PT_Bed'
        # 区域表
        hotelZoneTable = 'PT_Hotel_Zone'

        # 默认平台的组织ID
        default_organization_id = '7e7e2fec-d91d-11e9-8e1d-983b8f0bcd67'

        temp_organization = {}
        temp_user = {}
        temp_zone = {}
        temp_building = {}
        temp_floor = {}
        temp_room = {}

        # 数字格式转换
        numberChange = {
            '1': '一',
            '2': '二',
            '3': '三',
            '4': '四',
            '5': '五',
            '6': '六',
            '7': '七',
            '8': '八',
            '9': '九',
        }
        count = 0

        arr = []

        if len(condition) > 0:
            for item in condition:
                count = count + 1
                print('导入床位信息第'+str(count)+'个')

                # 所属机构
                if 'organization_code' in item and item['organization_code'] != None:
                    organization_code = self.clearZFC(
                        item['organization_code'])
                    organization_id = self._getOrganizationId(
                        organization_code, temp_organization, default_organization_id)
                    temp_organization[organization_code] = organization_id
                else:
                    organization_id = default_organization_id
                    temp_organization[organization_code] = default_organization_id

                # 判断有没有区域信息
                if 'zone_name' in item and item['zone_name'] != None:
                    # 区域的typeid
                    area_type_id = '574ccb70-36a5-11ea-95d5-a0a4c57e9ebe'
                    zone_name = self.clearZFC(item['zone_name'])
                    if zone_name in temp_zone:
                        zone_name_id = temp_zone[zone_name]
                    else:
                        _filter = MongoBillFilter()
                        _filter.match_bill((C('zone_name') == zone_name) & (C('organization_id') == organization_id) & (C('hotel_zone_type_id') == area_type_id))\
                            .project({'_id': 0})
                        res = self.query(
                            _filter, hotelZoneTable)

                        # 找出大区域的ID
                        if len(res) == 0:
                            zone_name_id = str(uuid.uuid1())
                            # 新增一个区域的信息
                            newArea = {
                                'id': zone_name_id,
                                'zone_name': zone_name,
                                'zone_number': zone_name,
                                'hotel_zone_type_id': area_type_id,
                                'remark': zone_name,
                                'organization_id': organization_id
                            }
                            self.bill_manage_service.add_bill(OperationType.add.value,
                                                              TypeId.hotelZone.value, newArea, hotelZoneTable)
                        else:
                            zone_name_id = res[0]['id']
                        # 缓存有问题，禁止缓存
                        # temp_zone[zone_name] = zone_name_id

                # 找出这个大区域下有没有这个楼的信息
                if 'building_name' in item and item['building_name'] != None:
                    # 栋楼的typeid
                    building_type_id = 'fb6562b6-ea32-11e9-b4aa-a0a4c57e9ebe'
                    building_name = self.clearZFC(item['building_name'])
                    if building_name in temp_building:
                        building_name_id = temp_building[building_name]
                    else:
                        _filter = MongoBillFilter()
                        _filter.match_bill((C('zone_name') == building_name) & (C('upper_hotel_zone_id') == zone_name_id) & (C('organization_id') == organization_id) & (C('hotel_zone_type_id') == building_type_id))\
                            .project({'_id': 0})
                        res = self.query(
                            _filter, hotelZoneTable)

                        # 找出这个楼的ID
                        if len(res) == 0:
                            building_name_id = str(uuid.uuid1())
                            # 新增一个楼的信息
                            newBuilding = {
                                'id': building_name_id,
                                'zone_name': building_name,
                                'zone_number': building_name,
                                'hotel_zone_type_id': building_type_id,
                                'hotel_zone_type_name': '栋',
                                'remark': building_name,
                                'upper_hotel_zone_id': zone_name_id,
                                'organization_id': organization_id
                            }
                            self.bill_manage_service.add_bill(OperationType.add.value,
                                                              TypeId.hotelZone.value, newBuilding, hotelZoneTable)
                        else:
                            building_name_id = res[0]['id']
                        # 缓存有问题，禁止缓存
                        # temp_building[building_name] = building_name_id

                # 找出这个楼下有没有这个层的信息
                if 'floor_name' in item and item['floor_name'] != None:
                    # 楼层的typeid
                    room_type_id = 'f0c2b50a-ea32-11e9-a294-a0a4c57e9ebe'
                    floor_name = self.clearZFC(
                        item['building_name']) + numberChange[self.clearZFC(item['floor_name'])] + '楼'
                    if floor_name in temp_floor:
                        floor_name = temp_floor[floor_name]
                    else:
                        _filter = MongoBillFilter()
                        _filter.match_bill((C('zone_name').like(floor_name)) & (C('upper_hotel_zone_id') == building_name_id) & (C('organization_id') == organization_id) & (C('hotel_zone_type_id') == room_type_id))\
                            .project({'_id': 0})
                        res = self.query(
                            _filter, hotelZoneTable)

                        # 找出这个层的ID
                        if len(res) == 0:
                            floor_name_id = str(uuid.uuid1())
                            # 新增一个楼层的信息
                            newFloor = {
                                'id': floor_name_id,
                                'zone_name': floor_name,
                                'zone_number': floor_name,
                                'hotel_zone_type_id': room_type_id,
                                'hotel_zone_type_name': '楼',
                                'remark': floor_name,
                                'upper_hotel_zone_id': building_name_id,
                                'organization_id': organization_id
                            }
                            self.bill_manage_service.add_bill(OperationType.add.value,
                                                              TypeId.hotelZone.value, newFloor, hotelZoneTable)
                        else:
                            floor_name_id = res[0]['id']
                        # 缓存有问题，禁止缓存
                        # temp_floor[floor_name] = floor_name_id

                # 根据最终找到的这个楼层的id，判断这个房是否已经录入
                if 'room_name' in item and item['room_name'] != None:
                    # 房的typeid
                    room_type_id = '01303228-ea33-11e9-b7d2-a0a4c57e9ebe'
                    room_name = self.clearZFC(item['room_name'])
                    if room_name[0] == '+':
                        search_room_name = '\\' + room_name
                    else:
                        search_room_name = room_name
                    if room_name in temp_room:
                        room_name_id = temp_room[room_name]
                    else:
                        _filter = MongoBillFilter()
                        _filter.match_bill((C('zone_name').like(search_room_name)) & (C('upper_hotel_zone_id') == floor_name_id) & (C('organization_id') == organization_id) & (C('hotel_zone_type_id') == room_type_id))\
                            .project({'_id': 0})
                        res = self.query(
                            _filter, hotelZoneTable)

                        if len(res) == 0:
                            room_name_id = str(uuid.uuid1())
                            # 新增一个房的信息
                            if floor_name[len(floor_name) - 1] != '楼':
                                floor_remark = floor_name + '楼-' + room_name
                            else:
                                floor_remark = floor_name + '-' + room_name
                            newFloor = {
                                'id': room_name_id,
                                'zone_name': numberChange[self.clearZFC(item['floor_name'])] + '楼-' + room_name,
                                'zone_number': numberChange[self.clearZFC(item['floor_name'])] + '楼-' + room_name,
                                'hotel_zone_type_id': room_type_id,
                                'hotel_zone_type_name': '房',
                                'remark': floor_remark,
                                'upper_hotel_zone_id': floor_name_id,
                                'organization_id': organization_id,
                            }
                            self.bill_manage_service.add_bill(OperationType.add.value,
                                                              TypeId.hotelZone.value, newFloor, hotelZoneTable)
                        else:
                            room_name_id = res[0]['id']
                        # 缓存有问题，禁止缓存
                        # temp_room[room_name] = room_name_id

                if 'id_card' in item and item['id_card'] != None:
                    id_card = self.clearZFC(item['id_card'])
                    residents_id = self._getUserId(id_card, temp_user)
                    temp_user[id_card] = residents_id
                else:
                    residents_id = ''
                    temp_user[id_card] = ''

                # 从区域到楼到层到房间都录好了，就去判断床位是否已经录入
                if 'bed_name' in item and item['bed_name'] != None:
                    bed_name = self.clearZFC(item['bed_name'])
                    # 根据房号和名字判断
                    keywords = search_room_name + '房-' + bed_name
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('name').like(keywords)) & (C('area_id') == room_name_id))\
                        .project({'_id': 0})
                    res = self.query(
                        _filter, bedTable)

                    if len(res) > 0:
                        # 更新
                        newData = self._setValues(keys, item, res[0])

                        # 导入标识
                        newData = self.setImportDate(newData, '20200116')
                        newData['residents_id'] = residents_id
                        newData['hotel_zone_type_name'] = '房'

                        # 处理时间
                        if 'checkin_date' in item and item['checkin_date'] != None:
                            newData['checkin_date'] = self.strtodate(
                                self.clearZFC(item['checkin_date']))
                        if 'create_date' in item and item['create_date'] != None:
                            newData['create_date'] = self.strtodate(
                                self.clearZFC(item['create_date']))
                        if 'end_date' in item and item['end_date'] != None:
                            newData['end_date'] = self.strtodate(
                                self.clearZFC(item['end_date']))

                        # 更新的
                        def process_func(db):
                            update_data(db, bedTable, newData, {
                                'id': newData['id'],
                                'bill_status': 'valid'
                            })
                        process_db("127.0.0.1", 27017, 'IEC',
                                   process_func, 'admin', '123456')
                    else:
                        # 新增
                        newData = {}
                        # 补充原数据
                        newData = self._setValues(keys, item, newData)

                        # 导入标识
                        newData = self.setImportDate(newData, '20200116')

                        room_name = self.clearZFC(item['room_name'])
                        # 新增一个楼层的信息
                        if room_name[len(room_name) - 1] != '房':
                            bedName = room_name + '房-' + bed_name + '床'
                        else:
                            bedName = room_name + '-' + bed_name + '床'

                        # 处理时间
                        if 'checkin_date' in item and item['checkin_date'] != None:
                            newData['checkin_date'] = self.strtodate(
                                self.clearZFC(item['checkin_date']))
                        if 'create_date' in item and item['create_date'] != None:
                            newData['create_date'] = self.strtodate(
                                self.clearZFC(item['create_date']))
                        if 'end_date' in item and item['end_date'] != None:
                            newData['end_date'] = self.strtodate(
                                self.clearZFC(item['end_date']))

                        # 补充数据
                        newData['area_id'] = room_name_id
                        newData['name'] = bedName
                        newData['bed_code'] = bedName
                        newData['remark'] = self.clearZFC(
                            item['building_name']) + '-' + bedName
                        newData['service_item_package_id'] = ''
                        newData['residents_id'] = residents_id
                        newData['organization_id'] = organization_id
                        newData['hotel_zone_type_name'] = '房'

                        newData = get_info(newData, self.session)

                        newData['GUID'] = str(uuid.uuid1())
                        newData['version'] = 1
                        newData['bill_status'] = 'valid'
                        newData['valid_bill_id'] = str(uuid.uuid1())
                        newData['bill_operator'] = '23b3630a-d92c-11e9-8b9a-983b8f0bcd67'

                        arr.append(newData)

                        # self.bill_manage_service.add_bill(OperationType.add.value,
                        #                             TypeId.bed.value, newData, bedTable)

        def process_func2(db):
            insert_many_data(db, bedTable, arr)
        process_db(self.db_addr, self.db_port, self.db_name,
                   process_func2, self.db_user, self.db_pwd)

    # 导入服务记录（工单）

    def import_excel_service_record(self, data):
        pf = pd.DataFrame(data)
        pf.rename(columns={'订单主键': 'order_primary_id', '工单主键': 'record_primary_id', '预约时间': 'book_date', '服务周期': 'periodicity', '派单时间': 'send_date', '工单号码': 'record_code', '工单状态': 'record_status', '工单时间': 'record_date', '开始时间': 'start_date', '回访状态': 'visit_status', '回访时间': 'visit_date',
                           '回访服务满意度': 'service_satisfaction', '服务机构主键': 'organization_code', '服务点': 'service_address', '服务人员': 'servicer_name', '服务人员身份证': 'service_person_id_card', '长者': 'elder_name', '长者身份证': 'elder_id_card', '工单完成时间': 'end_date', '是否删除': 'is_delete'}, inplace=True)
        condition = dataframe_to_list(pf)

        serviceOrderTable = 'ZPT_Service_Order'
        serviceRecordTable = 'ZPT_Service_Record'
        taskTable = 'ZPT_Task'

        keys = ['order_primary_id', 'record_primary_id', 'periodicity', 'record_code', 'record_status',
                'visit_status', 'service_satisfaction', 'service_address', 'elder_name', 'elder_id_card', 'elder_id']

        # 默认平台的组织ID
        default_organization_id = '7e7e2fec-d91d-11e9-8e1d-983b8f0bcd67'

        temp_organization = {}
        temp_worker = {}
        temp_elder = {}
        temp_order = {}

        arr = []
        arrTask = []

        count = 0

        if len(condition) > 0:
            for item in condition:

                count = count + 1

                newData = {}

                # 补充原数据
                newData = self._setValues(keys, item, newData)

                # 根据订单主键找回订单数据
                order_primary_id = self.clearZFC(item['order_primary_id'])
                if order_primary_id in temp_order:
                    res = temp_order[order_primary_id]
                else:
                    _filter = MongoBillFilter()
                    _filter.match_bill((C('order_primary_id') == order_primary_id))\
                        .project({'_id': 0})
                    res = self.query(
                        _filter, serviceOrderTable)
                    temp_order[order_primary_id] = res

                if len(res) == 0:
                    continue

                # 订单ID
                newData['order_id'] = res[0]['id']
                # 订单ID
                newData['service_product_id'] = res[0]['origin_product']['product_id']
                # 是否虚拟
                newData['is_virtual'] = '1'
                # 服务选项
                newData['service_option'] = []
                for itm in res[0]['detail']:
                    newData['service_option'].append(itm['service_option'])
                # 计价金额
                if 'amout' in res[0]:
                    newData['valuation_amount'] = res[0]['amout']
                # 内容
                if 'remark' in res[0]:
                    newData['content'] = res[0]['remark']
                # 状态
                newData['status'] = '1'

                # 是否删除
                if 'is_delete' in item and item['is_delete'] != None:
                    is_delete = self.clearZFC(item['is_delete'])
                    if is_delete == '1':
                        newData['is_delete'] = '是'
                    elif is_delete == '0':
                        newData['is_delete'] = '否'
                # 是否已回访
                if 'visit_status' in newData and newData['visit_status'] == '已回访':
                    newData['is_huifang'] = '是'
                else:
                    newData['is_huifang'] = '否'
                # 回访内容用回访状态
                if 'service_satisfaction' in newData:
                    newData['huifang_content'] = newData['service_satisfaction']
                # 预约时间
                if 'book_date' in item and item['book_date'] != None:
                    newData['book_date'] = self.strtodate(
                        self.clearZFC(item['book_date']))
                # 开始时间
                if 'start_date' in item and item['start_date'] != None:
                    newData['start_date'] = self.strtodate(
                        self.clearZFC(item['start_date']))
                # 结束时间
                if 'end_date' in item and item['end_date'] != None:
                    newData['end_date'] = self.strtodate(
                        self.clearZFC(item['end_date']))
                # 回访时间
                if 'visit_date' in item and item['visit_date'] != None:
                    newData['visit_date'] = self.strtodate(
                        self.clearZFC(item['visit_date']))
                # 派单时间
                if 'send_date' in item and item['send_date'] != None:
                    newData['send_date'] = self.strtodate(
                        self.clearZFC(item['send_date']))
                # 工单时间
                if 'record_date' in item and item['record_date'] != None:
                    newData['record_date'] = self.strtodate(
                        self.clearZFC(item['record_date']))

                # 所属机构
                if 'organization_code' in item and item['organization_code'] != None:
                    organization_code = self.clearZFC(
                        item['organization_code'])
                    organization_id = self._getOrganizationId(
                        organization_code, temp_organization, default_organization_id)
                    newData['organization_id'] = organization_id
                    temp_organization[organization_code] = organization_id
                else:
                    newData['organization_id'] = default_organization_id
                    temp_organization[organization_code] = default_organization_id

                # 服务商ID
                newData['servicer_id'] = newData['organization_id']

                # 服务人员
                if 'service_person_id_card' in item and item['service_person_id_card'] != None:
                    service_person_id_card = self.clearZFC(
                        item['service_person_id_card'])
                    service_person_id = self._getUserId(
                        service_person_id_card, temp_worker)
                    if service_person_id != '':
                        newData['service_person_id'] = service_person_id
                    temp_worker[service_person_id_card] = service_person_id

                # 购买者
                if 'elder_id_card' in item and item['elder_id_card'] != None:
                    elder_id_card = self.clearZFC(item['elder_id_card'])
                    elder_id = self._getUserId(elder_id_card, temp_elder)
                    if elder_id != '':
                        newData['elder_id'] = elder_id
                    temp_elder[elder_id_card] = elder_id

                # 导入标识
                newData = self.setImportDate(
                    newData, '20200113')

                data = get_info(newData, self.session)

                data['GUID'] = str(uuid.uuid1())
                data['version'] = 1
                data['bill_status'] = 'valid'
                data['valid_bill_id'] = str(uuid.uuid1())
                data['bill_operator'] = '23b3630a-d92c-11e9-8b9a-983b8f0bcd67'

                # 新任务
                newTask = {}

                # 任务信息
                newTask['task_name'] = res[0]['origin_product']['product_name']
                if 'remark' in res[0]:
                    newTask['task_content'] = res[0]['remark']
                if 'service_person_id' in data:
                    newTask['task_receive_person'] = data['service_person_id']
                if 'service_address' in data:
                    newTask['task_address'] = data['service_address']
                    newTask['task_location'] = data['service_address']
                if 'organization_id' in data:
                    newTask['task_creator'] = data['organization_id']
                    newTask['organization_id'] = data['organization_id']
                if 'elder_id' in data:
                    newTask['purchaser_id'] = data['elder_id']
                if 'start_date' in data:
                    newTask['start_date'] = data['start_date']
                if 'end_date' in data:
                    newTask['end_date'] = data['end_date']
                newTask['task_object_id'] = data['id']
                newTask['task_state'] = '已完成'
                newTask['task_type'] = '0b4b50d2-1d49-11ea-b9cb-a0a4c57e9ebe'
                newTask['task_urgent'] = '一般'

                # 导入标识
                newTask = self.setImportDate(
                    newTask, '20200113')

                taskData = get_info(newTask, self.session)

                taskData['GUID'] = str(uuid.uuid1())
                taskData['version'] = 1
                taskData['bill_status'] = 'valid'
                taskData['valid_bill_id'] = str(uuid.uuid1())
                taskData['bill_operator'] = '23b3630a-d92c-11e9-8b9a-983b8f0bcd67'

                print('服务工单第'+str(count)+'个')

                arr.append(data)
                arrTask.append(taskData)

        def process_func(db):
            insert_many_data(db, serviceRecordTable, arr)
            insert_many_data(db, taskTable, arrTask)
        process_db(self.db_addr, self.db_port, self.db_name,
                   process_func, self.db_user, self.db_pwd)

    # 导入活动义工
    def import_excel_volunteer(self, data):
        pf = pd.DataFrame(data)
        pf.rename(columns={'机构编号': 'organization_code', '活动名称': 'activity_name', '义工名字': 'name',
                           '身份证号': 'id_card', '性别': 'sex', '手机': 'telephone', '生日': 'date_birth'}, inplace=True)
        condition = dataframe_to_list(pf)

        activityVolunteerTable = 'ZPT_Activity_Volunteer'
        activityTable = 'ZPT_Activity'

        keys = ['name', 'id_card', 'telephone']

        # 默认平台的组织ID
        default_organization_id = '7e7e2fec-d91d-11e9-8e1d-983b8f0bcd67'

        temp_organization = {}
        temp_activity = {}
        temp_user = {}

        arr = []

        count = 0

        if len(condition) > 0:
            for item in condition:

                count = count + 1

                newData = {}
                # 补充原数据
                newData = self._setValues(keys, item, newData)

                # 所属机构
                if 'organization_code' in item and item['organization_code'] != None:
                    organization_code = self.clearZFC(
                        item['organization_code'])
                    organization_id = self._getOrganizationId(
                        organization_code, temp_organization, default_organization_id)
                    newData['organization_id'] = organization_id
                    temp_organization[organization_code] = organization_id
                else:
                    newData['organization_id'] = default_organization_id

                # 根据活动名字找到id
                if 'activity_name' in item and item['activity_name'] != None:
                    activity_name = self.clearZFC(item['activity_name'])

                    _filter = MongoBillFilter()
                    _filter.match_bill((C('activity_name') == activity_name))\
                        .project({'_id': 0})
                    res = self.query(
                        _filter, activityTable)

                    if len(res) > 0:
                        newData['activity_id'] = res[0]['id']
                        temp_activity[activity_name] = res[0]['id']
                    else:
                        newData['activity_id'] = activity_name
                        temp_activity[activity_name] = activity_name

                # 根据所属机构和人员名字找到人员id
                if 'id_card' in item and item['id_card'] != None:
                    id_card = self.clearZFC(item['id_card'])
                    user_id = self._getUserId(id_card, temp_user)
                    if user_id != '':
                        newData['user_id'] = user_id
                    temp_user[id_card] = user_id

                # 人员生日
                if 'date_birth' in item and item['date_birth'] != None:
                    newData['date_birth'] = self.strtodate(
                        self.clearZFC(item['date_birth']))

                # 人员性别
                if 'sex' in item and item['sex'] != None:
                    sex = self.clearZFC(item['sex'])
                    if sex == 'Male':
                        newData['sex'] = '男'
                    elif sex == 'Female':
                        newData['sex'] = '女'

                # 导入标识
                newData = self.setImportDate(
                    newData, '20200113')

                data = get_info(newData, self.session)

                data['GUID'] = str(uuid.uuid1())
                data['version'] = 1
                data['bill_status'] = 'valid'
                data['valid_bill_id'] = str(uuid.uuid1())
                data['bill_operator'] = '23b3630a-d92c-11e9-8b9a-983b8f0bcd67'

                print('活动义工第'+str(count)+'个')

                arr.append(data)

        def process_func(db):
            insert_many_data(db, activityVolunteerTable, arr)
        process_db(self.db_addr, self.db_port, self.db_name,
                   process_func, self.db_user, self.db_pwd)

    # 导入活动签到

    def import_excel_signin(self, data):
        pf = pd.DataFrame(data)
        pf.rename(columns={'机构编号': 'organization_code', '活动名称': 'activity_name', '活动报名': 'activity_signin', '参加状态': 'signin_status', '签到模式': 'signin_mode', '报名状态': 'enroll_status', '报名方式': 'enroll_type', '报名时间': 'enroll_date',
                           '参加人员姓名': 'name', '性别': 'sex', '身份证': 'id_card', '生日': 'date_birth', '活动报名人数': 'enroll_count'}, inplace=True)
        condition = dataframe_to_list(pf)

        activitySignInTable = 'ZPT_Activity_Sign_In'
        activityTable = 'ZPT_Activity'

        keys = ['activity_signin', 'signin_status', 'signin_mode',
                'enroll_status', 'enroll_type', 'enroll_count', 'id_card', 'name']

        # 默认平台的组织ID
        default_organization_id = '7e7e2fec-d91d-11e9-8e1d-983b8f0bcd67'

        temp_organization = {}
        temp_activity = {}
        temp_user = {}

        arr = []

        count = 0

        if len(condition) > 0:
            for item in condition:

                count = count + 1

                newData = {}

                # 补充原数据
                newData = self._setValues(keys, item, newData)

                # 所属机构
                if 'organization_code' in item and item['organization_code'] != None:
                    organization_code = self.clearZFC(
                        item['organization_code'])
                    organization_id = self._getOrganizationId(
                        organization_code, temp_organization, default_organization_id)
                    newData['organization_id'] = organization_id
                    temp_organization[organization_code] = organization_id
                else:
                    newData['organization_id'] = default_organization_id

                # 根据活动名字找到id
                if 'activity_name' in item and item['activity_name'] != None:
                    activity_name = self.clearZFC(item['activity_name'])

                    _filter = MongoBillFilter()
                    _filter.match_bill((C('activity_name') == activity_name))\
                        .project({'_id': 0})
                    res = self.query(
                        _filter, activityTable)

                    if len(res) > 0:
                        newData['activity_id'] = res[0]['id']
                        temp_activity[activity_name] = res[0]['id']
                    else:
                        newData['activity_id'] = activity_name
                        temp_activity[activity_name] = activity_name

                # 根据所属机构和人员名字找到人员id
                if 'id_card' in item and item['id_card'] != None:
                    id_card = self.clearZFC(item['id_card'])
                    user_id = self._getUserId(id_card, temp_user)
                    if user_id != '':
                        newData['user_id'] = user_id
                    temp_user[id_card] = user_id

                # 签到日期
                if 'enroll_date' in item and item['enroll_date'] != None:
                    newData['date'] = self.strtodate(
                        self.clearZFC(item['enroll_date']))

                # 签到人员生日
                if 'date_birth' in item and item['date_birth'] != None:
                    newData['date_birth'] = self.strtodate(
                        self.clearZFC(item['date_birth']))

                # 签到人员性别
                if 'sex' in item and item['sex'] != None:
                    sex = self.clearZFC(item['sex'])
                    if sex == 'Male':
                        newData['sex'] = '男'
                    elif sex == 'Female':
                        newData['sex'] = '女'

                # 导入标识
                newData = self.setImportDate(
                    newData, '20200113')

                data = get_info(newData, self.session)

                data['GUID'] = str(uuid.uuid1())
                data['version'] = 1
                data['bill_status'] = 'valid'
                data['valid_bill_id'] = str(uuid.uuid1())
                data['bill_operator'] = '23b3630a-d92c-11e9-8b9a-983b8f0bcd67'

                print('活动签到第'+str(count)+'个')

                arr.append(data)

        def process_func(db):
            insert_many_data(db, activitySignInTable, arr)
        process_db(self.db_addr, self.db_port, self.db_name,
                   process_func, self.db_user, self.db_pwd)

    # 导入活动室
    def import_excel_activity_room(self, data):
        pf = pd.DataFrame(data)
        pf.rename(columns={'活动室名称': 'activity_room_name', '机构编号': 'organization_code', '活动室容纳人数': 'max_quantity', '活动室功能': 'remarks', '活动室开放时间': 'begin_date', '活动室关闭时间': 'end_date', '所在楼层': 'floor', '房间编号': 'room_code',
                           '大楼名称': 'address'}, inplace=True)
        condition = dataframe_to_list(pf)

        activityRoomTable = 'ZPT_Activity_Room'

        keys = ['activity_room_name', 'max_quantity', 'remarks',
                'floor', 'room_code', 'contacts', 'phone', 'address']

        # 默认平台的组织ID
        default_organization_id = '7e7e2fec-d91d-11e9-8e1d-983b8f0bcd67'

        temp_organization = {}

        arr = []

        count = 0

        if len(condition) > 0:

            for item in condition:
                count = count + 1
                # 新增的活动室数据
                newActivityRoom = {}

                # 补充原数据
                newActivityRoom = self._setValues(keys, item, newActivityRoom)

                # 开始时间
                if 'begin_date' in item and item['begin_date'] != None:
                    begin_date = self.clearZFC(item['begin_date'])
                    newActivityRoom['begin_date'] = self._setTimeFormat(
                        begin_date)
                # 结束时间
                if 'end_date' in item and item['end_date'] != None:
                    end_date = self.clearZFC(item['end_date'])
                    newActivityRoom['end_date'] = self._setTimeFormat(end_date)
                # 所属机构
                if 'organization_code' in item and item['organization_code'] != None:
                    organization_code = self.clearZFC(
                        item['organization_code'])
                    organization_id = self._getOrganizationId(
                        organization_code, temp_organization, default_organization_id)
                    newActivityRoom['organization_id'] = organization_id
                    temp_organization[organization_code] = organization_id
                else:
                    newActivityRoom['organization_id'] = default_organization_id
                # 导入标识
                newActivityRoom = self.setImportDate(
                    newActivityRoom, '20200110')

                data = get_info(newActivityRoom, self.session)

                data['GUID'] = str(uuid.uuid1())
                data['version'] = 1
                data['bill_status'] = 'valid'
                data['valid_bill_id'] = str(uuid.uuid1())
                data['bill_operator'] = '23b3630a-d92c-11e9-8b9a-983b8f0bcd67'

                print('活动室第'+str(count)+'个')

                arr.append(data)

        def process_func(db):
            insert_many_data(db, activityRoomTable, arr)
        process_db(self.db_addr, self.db_port, self.db_name,
                   process_func, self.db_user, self.db_pwd)

    # 导入活动
    def import_excel_activity(self, data):

        pf = pd.DataFrame(data)
        pf.rename(columns={'机构名称': 'organization_name', '机构编号': 'organization_code', '活动名称': 'activity_name', '活动图片': 'photo', '最大参与人数': 'max_quantity', '活动发布时间': 'create_date', '活动开始时间': 'begin_date',
                           '活动结束时间': 'end_date', '活动地点': 'address', '活动室': 'activity_room', '活动室所在大楼': 'activity_room_address', '联系人': 'contacts', '联系人电话': 'phone', '活动金额': 'amount', '活动介绍': 'introduce', '活动状态': 'status', '活动类型': 'activity_type_id', '报名人数': 'enroll_count', '活动报告': 'activity_report', '活动报告图片': 'activity_report_picture'}, inplace=True)
        condition = dataframe_to_list(pf)

        activityTypeTable = 'ZPT_Activity_Type'
        activityTable = 'ZPT_Activity'

        keys = ['activity_name', 'max_quantity', 'address', 'activity_room', 'activity_room_address',
                'contacts', 'phone', 'amount', 'introduce', 'enroll_count', 'activity_report']

        # 默认平台的组织ID
        default_organization_id = '7e7e2fec-d91d-11e9-8e1d-983b8f0bcd67'

        temp_type_id = {}
        temp_organization = {}

        arr = []

        count = 0

        if len(condition) > 0:

            for item in condition:
                count = count + 1
                # 新增的活动数据
                newActivity = {}

                # 补充原数据
                newActivity = self._setValues(keys, item, newActivity)

                # 开始时间
                if 'begin_date' in item and item['begin_date'] != None:
                    begin_date = self.clearZFC(item['begin_date'])
                    newActivity['begin_date'] = self._setTimeFormat(begin_date)
                # 结束时间
                if 'end_date' in item and item['end_date'] != None:
                    end_date = self.clearZFC(item['end_date'])
                    newActivity['end_date'] = self._setTimeFormat(end_date)
                # 所属机构
                if 'organization_code' in item and item['organization_code'] != None:
                    organization_code = self.clearZFC(
                        item['organization_code'])
                    organization_id = self._getOrganizationId(
                        organization_code, temp_organization, default_organization_id)
                    newActivity['organization_id'] = organization_id
                    temp_organization[organization_code] = organization_id
                else:
                    newActivity['organization_id'] = default_organization_id

                # 活动状态
                if 'status' in item and item['status'] != None:
                    status = self.clearZFC(item['status'])
                    if status in ActivityStatus.__members__:
                        newActivity['status'] = ActivityStatus[status].value
                    else:
                        newActivity['status'] = ''

                # 照片
                if 'photo' in item and item['photo'] != None:
                    photo = self.get_url_upload_url(
                        self.clearZFC(item['photo']), 'activityPhoto')
                    if photo != '':
                        newActivity['photo'] = [photo]
                else:
                    newActivity['photo'] = []
                if 'activity_report_picture' in item and item['activity_report_picture'] != None:
                    activity_report_picture = self.get_url_upload_url(
                        self.clearZFC(item['activity_report_picture']), 'activityPhoto')
                    if photo != '':
                        newActivity['activity_report_picture'] = [
                            activity_report_picture]

                if 'activity_type_id' in item and item['activity_type_id'] != None:
                    activity_type_id = self.clearZFC(item['activity_type_id'])
                    if activity_type_id in temp_type_id:
                        newActivity['activity_type_id'] = temp_type_id[activity_type_id]
                    else:
                        _filter = MongoBillFilter()
                        _filter.match_bill((C('keys') == activity_type_id))\
                            .project({'_id': 0})
                        res = self.query(
                            _filter, activityTypeTable)

                        if len(res) > 0:
                            newActivity['activity_type_id'] = res[0]['id']
                            temp_type_id[activity_type_id] = res[0]['id']
                        else:
                            newActivity['activity_type_id'] = activity_type_id
                            temp_type_id[activity_type_id] = activity_type_id

                newActivity['is_enable'] = True
                newActivity['is_sign_in'] = True
                newActivity['step_no'] = -1

                # 导入标识
                newActivity = self.setImportDate(newActivity, '20200110')

                data = get_info(newActivity, self.session)

                # 创建时间
                if 'create_date' in item and item['create_date'] != None:
                    create_date = self.clearZFC(item['create_date'])
                    newActivity['create_date'] = self._setTimeFormat(
                        create_date)

                data['GUID'] = str(uuid.uuid1())
                data['version'] = 1
                data['bill_status'] = 'valid'
                data['valid_bill_id'] = str(uuid.uuid1())
                data['bill_operator'] = '23b3630a-d92c-11e9-8b9a-983b8f0bcd67'

                print('活动列表第'+str(count)+'个')

                arr.append(data)

        def process_func(db):
            insert_many_data(db, activityTable, arr)
        process_db(self.db_addr, self.db_port, self.db_name,
                   process_func, self.db_user, self.db_pwd)

    def import_excel_personnel55(self, data):
        pf = pd.DataFrame(data)
        pf.rename(columns={'姓名': 'xm', '性别': 'xb', '出生日期': 'csrq', '身份证号码': 'sfzhm', '所属机构': 'ssjg', '联系电话': 'lxdh',
                           'jg': '籍贯', '家庭地址': 'jtdz', '身份证地址': 'sfzdz', '行政区划编号': 'xzqybh', '行政区域': 'xzqy', '照片': 'zp', '学历资质': 'xlzz', '岗位': 'gw', '角色': 'js', '护理区域': 'hlqy', '人员类别': 'rylb'}, inplace=True)
        condition = dataframe_to_list(pf)

        tempJG = {}
        tempJGD = []

        if len(condition) > 0:
            for item in condition:
                # ssjg = str(item['ssjg'])
                ssjg = str(item['ssjg']).replace(
                    '\r', '').replace('\n', '').replace('\t', '')
                _filter_ssjg = MongoBillFilter()
                _filter_ssjg.match_bill((C('name') == ssjg))\
                    .project({'_id': 0})
                ssjg_res = self.query(
                    _filter_ssjg, 'PT_User56')

                if len(ssjg_res) == 0:
                    if ssjg not in tempJG:
                        tempJGD.append(ssjg)
                        tempJG[ssjg] = ssjg

        print(tempJGD)

    def import_excel_personnel4(self, data):

        pf = pd.DataFrame(data)
        pf.rename(columns={'名称': 'mc', '英文名称': 'ywmc', '编号': 'bh', '简称': 'jc', '首府': 'sf', '经度': 'jd',
                           '纬度': 'wd', '行政区域级别': 'xzqyjb', '上级名称': 'sjmc', '上级行政区域（编号）': 'sjxzqybh'}, inplace=True)
        # pf = pf.drop(['age', 'replacement_amount', 'total_amount', 'title'], axis=1)
        condition = dataframe_to_list(pf)

        tempBH = {}

        # return condition

        if len(condition) > 0:

            count = 0

            for item in condition:
                # 导入行政区域
                newData = {
                    "name": "",
                    "english_name": "",
                    "code": "",
                    "short_name": "",
                    "capital": '',
                    "location": {
                        "lon": "",
                        "lat": ""
                    },
                    "division_level": "",
                    "parent_id": "",
                }
                if 'mc' in item and item['mc'] != None:
                    newData['name'] = item['mc']
                if 'ywmc' in item and item['ywmc'] != None:
                    newData['english_name'] = item['ywmc']
                if 'bh' in item and item['bh'] != None:
                    newData['code'] = item['bh']
                if 'jc' in item and item['jc'] != None:
                    newData['short_name'] = item['jc']
                if 'sf' in item and item['sf'] != None:
                    newData['capital'] = item['sf']
                if 'jd' in item and item['jd'] != None:
                    newData['location']['lon'] = item['jd']
                if 'wd' in item and item['wd'] != None:
                    newData['location']['lat'] = item['wd']
                if 'xzqyjb' in item and item['xzqyjb'] != None:
                    newData['division_level'] = item['xzqyjb']
                if 'sjxzqybh' in item and item['sjxzqybh'] != None:
                    sjxzqybh = str(item['sjxzqybh'])
                    if sjxzqybh in tempBH:
                        print('已有上级区域，无需重复获取')
                        newData['parent_id'] = tempBH[sjxzqybh]
                    else:
                        _filter_bianhao = MongoBillFilter()
                        _filter_bianhao.match_bill((C('code') == sjxzqybh))\
                            .project({'_id': 0})
                        bh_info = self.query(
                            _filter_bianhao, 'PT_Administration_Division56')

                        if len(bh_info) > 0:
                            tempBH[sjxzqybh] = bh_info[0]['id']
                            newData['parent_id'] = bh_info[0]['id']

                # return newData

                data = get_info(newData, self.session)

                count = count + 1
                print('开始导入第' + str((count)) + '个')
                try:
                    self.bill_manage_service.add_bill(OperationType.add.value,
                                                      TypeId.administrationDivision.value, data, 'PT_Administration_Division56')
                except Exception as e:
                    print(e)
        return '导入成功'

    def get_url_upload_url(self, url_path, path_name='importProduct'):
        ''' 通过url导入图片 '''
        try:
            request = urllib.request.Request(url_path)
            response = urllib.request.urlopen(request)
        except Exception as e:
            return ''
        get_img = response.read()
        import string
        import random
        #####保存图片#########
        file_name = str(int(time.time())) + "_" + str(
            "".join(random.choice(string.ascii_letters + string.digits) for _ in range(8)))
        upload_path = "{0}\\{1}".format('upload', path_name)  # 以xfxz命名文件
        file_path = os.path.join('build', upload_path).replace('\\', '/')
        if not os.path.exists(file_path):
            # 不存在改目录则会自动创建
            os.makedirs(file_path)
        save_path = os.path.join(file_path, file_name+'.jpg').replace(
            '\\', '/')
        #  windows下路径要转化
        with open(save_path, 'wb') as fp:
            fp.write(get_img)
        return '/'+save_path

    # 从字符串中提取数字
    def _getNumberFromString(self, string):
        result = re.findall(r"\d+\.?\d*", string)
        if len(result) > 0:
            return result[0]
        else:
            return string

    # 设置导入标识

    def setImportDate(self, data, no):
        # 导入标识
        if 'import_date' in data:
            if type(data['import_date']) == str:
                # 新标识
                if data['import_date'] != no:
                    newArr = []
                    newArr.append(data['import_date'])
                    newArr.append(no)
                else:
                    newArr = []
                    newArr.append(no)
                data['import_date'] = newArr
                return data
            elif type(data['import_date']) == list:
                if no in data['import_date']:
                    return data
                else:
                    data['import_date'].append(no)
                return data
        else:
            data['import_date'] = []
            data['import_date'].append(no)
        return data
